How To Know System Date Format

[devmaneesh]

Sir,
I want to know the code to retrieve the system date format, whether it is dd/MM/yyyy or MM/dd/yyyy or else.

[leinad31]

For what purpose? Maybe you can change the process instead.

[westconn1]

dim d as date
d= "03/04/05"
if format(month(d), "mmm") = "March" then msgbox("date format = mm/dd")
if format(month(d), "mmm") = "April" then msgbox("date format = dd/mm")

i haven't tested this, but you should get the idea, can also use to test the if yy or yyyy etc

[leinad31]

If you can circumvent or skip working with dates held in strings (work only with datetime data type) then the format would not be an issue.

So again, for what purpose? Maybe the issue is with the process.

[devmaneesh]

Actually I have created a software in which I want an alert message that the system format is not in 'dd/MM/yyyy' if it is not. Because, if it is so, my Database(mdb file) will store data in wrong format and I will get incorect data.

[westconn1]

really you should change your code to make sure you data staroge is right regardless of the system date format

[devmaneesh]

i just want to know that is there any way/method to find out the system short date format

[Mr.Mac]

In the following, Date = "31-12-2005" works as follows
VB sees the dashes and thus 3 components.
It sets the year to 2005 since it sees 4 digits.
It sets the day to 31 since it was greater than 12.
It sets the month to 12 since it was less than 13.
Since 12,31,20,and 05 are unique, we can find the components of date

Mac

Option Explicit
Dim DateFormat As String

Private Sub Form_Load()
Dim Keep As Date
Keep = Date ' <--------- Save current date
Date = "31-12-2005" ' <- Replace
DateFormat = Date ' <--- Get date in user's format
Date = Keep ' <--------- Restore current date
Mid$(DateFormat, InStr(DateFormat, "31"), 2) = "DD"
Mid$(DateFormat, InStr(DateFormat, "12"), 2) = "MM"
Mid$(DateFormat, InStr(DateFormat, "05"), 2) = "YY"
Dim y As Integer: y = InStr(DateFormat, "20")
If y > 0 Then Mid$(DateFormat, y, 2) = "YY"
End Sub

Private Sub Command1_Click()
MsgBox "Your system date format is " + DateFormat
End Sub

[CVMichael]

Here:
Put this code in a module:

Option Explicit

Private Declare Function WinEnumDateFormats Lib "kernel32" Alias "EnumDateFormatsA" (ByVal lpDateFmtEnumProc As Long, ByVal Locale As Long, ByVal dwFlags As Long) As Long
Private Declare Sub WinCopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByVal lpDestination As Any, ByVal lpSource As Long, ByVal Length As Long)
Private Declare Function Winlstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Long) As Long

Private Const LOCALE_SYSTEM_DEFAULT     As Long = &H400
Private Const DATE_LONGDATE             As Long = &H2
Private Const DATE_SHORTDATE            As Long = &H1

Private m_sDateFormat   As String

Public Function GetLongDateFormat() As String
    WinEnumDateFormats AddressOf bPiEnumDateFormatsProc, LOCALE_SYSTEM_DEFAULT, DATE_LONGDATE
    
    GetLongDateFormat = Replace(m_sDateFormat, Chr(0), "")
End Function

Public Function GetShortDateFormat() As String
    WinEnumDateFormats AddressOf bPiEnumDateFormatsProc, LOCALE_SYSTEM_DEFAULT, DATE_SHORTDATE
    
    GetShortDateFormat = Replace(m_sDateFormat, Chr(0), "")
End Function

Private Function bPiEnumDateFormatsProc(ByVal lpstrFormat As Long) As Long
    Dim sBuffer     As String
    Dim lSize       As Long
    
    lSize = Winlstrlen(lpstrFormat)
    
    sBuffer = String$(lSize + 1, vbNullChar)
    
    WinCopyMemory sBuffer, lpstrFormat, lSize
    m_sDateFormat = Left$(sBuffer, InStr(sBuffer, vbNullChar))
    
    bPiEnumDateFormatsProc = True
End Function

To get the date format, just call any of these 2 functions:

    Debug.Print GetLongDateFormat
    Debug.Print GetShortDateFormat

I get this result:

dddd, MMMM dd, yyyy
dd/MM/yyyy

[Mr.Mac]

Some bugs in my previous post I found by changing my system date to miscellaneous crazy formats.

This one appears to work.

Mac

Option Explicit
Dim dF As String

Private Sub Form_Load()
Dim zz As Date
zz = "31-12-2006"
If InStr(zz, "31") > InStr(zz, "12") Then
  zz = "07-08-2009"
Else
  zz = "08-07-2009"
End If
dF = zz
Dim y As Integer
y = InStr(dF, "7")
If y > 0 Then
  Mid$(dF, y, 1) = "M"
  y = InStr(dF, "0M"): If y > 0 Then Mid$(dF, y, 1) = "M"
End If
y = InStr(dF, "8")
If y > 0 Then
  Mid$(dF, y, 1) = "D"
  y = InStr(dF, "0D"): If y > 0 Then Mid$(dF, y, 1) = "D"
  y = InStr(dF, "0D"): If y > 0 Then Mid$(dF, y, 1) = "D"
End If
y = InStr(dF, "9")
If y > 0 Then
  Mid$(dF, y, 1) = "Y"
  y = InStr(dF, "0Y"): If y > 0 Then Mid$(dF, y, 1) = "Y"
  y = InStr(dF, "0Y"): If y > 0 Then Mid$(dF, y, 1) = "Y"
  y = InStr(dF, "2Y"): If y > 0 Then Mid$(dF, y, 1) = "Y"
End If
End Sub

Private Sub Command1_Click()
MsgBox "Your system date format is " + dF
End Sub

[leinad31]

How would the database get erroneous data?

If your storing info in a string field in the database then you would know its format, to convert it to valid datetime with no error then you'd prse it and use DateSerial().

If your storing in database in datatime data type then again how are you passing values to the database? In your insert queries use mmm dd yyyy format so month is interpreted. If your using datepicker control to accept user input then there's no issue cause it passes datetime data type. If your using textbox for user input then validate it accordingly and don't rely on CDate() so conversion is not PC regional settings dependent... use DateSerial().

[Hack]

If all you are after is the regional settings date format, try this

Option Explicit

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long

Private Const LOCALE_USER_DEFAULT = &H400
Private Const LOCALE_SSHORTDATE = &H1F

Private Function GetDateFormat() As String
    Dim lBuffLen As Long
    Dim sBuffer As String
    Dim lResult As Long
    Dim sDateFormat As String
    
    
    lBuffLen = 128
    sBuffer = String$(lBuffLen, vbNullChar)
    
    lResult = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, sBuffer, lBuffLen)
    
    If lResult > 0 Then
        sDateFormat = Left$(sBuffer, lResult - 1)

         If InStr(1, sDateFormat, "YYYY", vbTextCompare) = 0 Then
             Replace sDateFormat, "YY", "YYYY"
         End If
        
         GetDateFormat = sDateFormat
    Else
        GetDateFormat = "DD/MM/YYYY"
    End If
    Exit Function

End Function

Private Sub Command1_Click()
Dim strFormat As String
strFormat = GetDateFormat
MsgBox strFormat
End Sub

[si_the_geek]

As leinad31 and westconn1 have said, the date settings do not matter - you can safely read/write date values from a database without much issue, it is just a matter of the code you use.

If you are using a recordset simply use a Date (not string) value, and if you are using SQL statements then format the Date values appropriately (eg: "yyyy-mm-dd", or "mm/dd/yyyy").

If you show us the code that has problems, we can help you correct it.

[Mr.Mac]

I want to know the code to retrieve the system date format

As you haven't closed this thread yet, I presume you are still trying to decide what to do:

1) Quit worrying about date format, as recommended by leinad31, westconn1 and si_the_geek - Fix your non-VB system so it is independent of the system date format (if it isn't already).

2) Use the short and sweet GetLocaleInfo method suggested by Hack.

3) Use the pure VB method suggested by me.

I lean toward "1". But if you insist, I like 3 more than 2 because it is self-contained in one function (doesn't require any declares at the beginning of your program).

Below is a test. It shows the results of 2 and 3 so you can see they both work. Try testdata like "mm mmmm yy dddd dd dd" or whatever for system date format. (Make sure to restore your original when finished testing).

I hope you can mark this thread "Resolved" now.

Mac

Option Explicit
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long

Private Const LOCALE_USER_DEFAULT = &H400
Private Const LOCALE_SSHORTDATE = &H1F

Private Sub Command1_Click()
Label1.Caption = UCase$(GetDateFormat1)
Label2.Caption = GetDateFormat2
End Sub

Private Function GetDateFormat1() As String
Dim lBuffLen As Long: lBuffLen = 128
Dim sBuffer As String, lResult As Long, sDateFormat As String
sBuffer = String$(lBuffLen, vbNullChar)
lResult = GetLocaleInfo(LOCALE_USER_DEFAULT, _
          LOCALE_SSHORTDATE, sBuffer, lBuffLen)
If lResult > 0 Then
  sDateFormat = Left$(sBuffer, lResult - 1)
  GetDateFormat1 = sDateFormat
Else
  MsgBox "Unable to read format - Using 'Unknown'"
  GetDateFormat1 = "Unknown"
End If
End Function

Private Function GetDateFormat2() As String
Dim dteZ As Date: dteZ = "07-08-2009"
If Month(dteZ) <> 7 Then dteZ = "08-07-2009"
Dim dF As String, strZ As String, y As Integer
dF = dteZ
Do
  strZ = dF
  y = InStr(dF, "7")
  If y > 0 Then
    Mid$(dF, y, 1) = "M"
    y = InStr(dF, "0M"): If y > 0 Then Mid$(dF, y, 1) = "M"
  End If
  y = InStr(dF, "8")
  If y > 0 Then
    Mid$(dF, y, 1) = "D"
    y = InStr(dF, "0D"): If y > 0 Then Mid$(dF, y, 1) = "D"
    y = InStr(dF, "0D"): If y > 0 Then Mid$(dF, y, 1) = "D"
  End If
  y = InStr(dF, "9")
  If y > 0 Then
    Mid$(dF, y, 1) = "Y"
    y = InStr(dF, "0Y"): If y > 0 Then Mid$(dF, y, 1) = "Y"
    y = InStr(dF, "0Y"): If y > 0 Then Mid$(dF, y, 1) = "Y"
    y = InStr(dF, "2Y"): If y > 0 Then Mid$(dF, y, 1) = "Y"
  End If
  y = InStr(dF, "Wednesday")
  If y > 0 Then
    Mid$(dF, y, 4) = "DDDD"
    dF = Left$(dF, y + 3) + Right$(dF, Len(dF) - y - 8)
  End If
  y = InStr(dF, "July"): If y > 0 Then Mid$(dF, y, 4) = "MMMM"
Loop While strZ <> dF
Do
  strZ = dF
  y = InStr(dF, "Wed"): If y > 0 Then Mid$(dF, y, 3) = "DDD"
  y = InStr(dF, "Jul"): If y > 0 Then Mid$(dF, y, 3) = "MMM"
Loop While strZ <> dF
GetDateFormat2 = dF
End Function

[b2000]

Hi,
I think ur requirement is,if the date format is not dd/MM/yyyy, then u need an alert message.
Then try this
Write this code in module and use this boolean function in the form load.

Public Function DateFormatChk() As Boolean
Dim Date1 As String
Dim Date2 As String
Date1 = Now
Date2 = Format(Now, "dd/MM/yyyy")
If Mid(Date1, 1, 10) <> Mid(Date2, 1, 10) Or Mid(Date2, 3, 1) <> "/" then
MsgBox "Please Change the Date format"
DateFormatChk = False
Else
DateFormatChk = True
End If
End Function

private sub form_load()
If DateFormatChk = False Then
End
End If
end sub

[Mr.Mac]

MsgBox "Please Change the Date format"

Welcome to the forum!

LOL! It looks like we are going to keep answering this question until the OP marks it RESOLVED.

I believe that although this has nothing to do with VB, this forum's general consensus it that it is very dangerous and a bad practice to require users to go change their system date format. No matter how non-standard their format might be, their whole system is working and dependent on it. Who knows what breaks if the system date format is changed?

It would be great if all computers had a manditory format of yyyy-mm-dd. But NOOOOO! So I am stuck with m/d/yy forever since I noticed it too late and have a zillion tailor-made programs made in the ignorance of thinking DATE$ was a fixed format and the only source of the date.

Of course, it would also be great if all programmers would code programs that are independent of the system date format. For example, I should have used MONTH(),DAY(),YEAR() instead of parsing DATE$. Oh, well, live and learn.

For years I used DATE$ in QBasic. It always returns the date in the format mm-dd-yyyy regardless of the system date format.

Mac

[jcis]

...Write this code in module and use this boolean function in the form load...

2 things:

1) Never use End.
2) How would that work for dates like: 01/01/2007 or 02/02/2007 or 03/03/2007..?

[leinad31]

Its unfortunate, but there are still people who think of and code with dates in terms of its string representation because they are unaware that they are seeing only a "friendly" version of the datetime data type stored as an offset from a starting date... perhaps they should try debug.print CDec(Now) instead of simply debug.print Now... and they are also unaware that CDate() is unreliable as it will move around the numeric values (from mm, dd, and yy in any order) in an attempt to create a valid date that will conform with PC settings. DateTime to string such as with format is ok... reversing the process can have surprising results that's why its best to avoid it and stick with datetime datatypes as much as possible... and to use DateSerial() rather than CDate().

[si_the_geek]

...as recommended by leinad31, westconn1 and si_the_geek - Fix your non-VB system so it is independent of the system date format (if it isn't already).

That's not what we meant at all - we meant the problem is in the VB code.

These are known issues (basically what leinad31 posted), and they have known solutions. An explanation of some can be seen in this FAQ.

Rather than put in a lot of effort to basically say "my program doesn't work properly, so please change your computer settings", it would be better to correct the problem, so that it will work no matter what the system date format is.

These corrections are often simple, needing little (if any) more code than there is already... if devmaneesh lets us know the code that is being used to write dates to the database, we can show how to fix it.

[Mr.Mac]

That's not what we meant at all - we meant the problem is in the VB code.

Boy, I missed that. Still a little confusing. "Database(mdb file)". I thought the OP had produced or purchased some non-VB software that would automatically include the date in the system date format. So he wanted to produce a program to warn the users to change the system date format. (A crazy concept, but that is what it seemed like).

If the OP was actually writing the date from a VB program, the solution is so simple as to be ridiculous. Just use Month(Date), Day(Date), Year(Date). That's all. Who cares what the system date format is?

Mac

[si_the_geek]

Unfortunately that isn't always a valid solution (especially when the 'date' comes from a textbox or similar), and is actually very similar to using the Format function.. which is also prone to the kind of errors that jcis implied.

When converting from text to any kind of date, you really need to know what the format of that text is (eg: d/m/yy), and should convert it based on the position of items along with DateSerial etc, rather than allow implicit conversion at any point (functions like Format and Month use an implicit CDate).

Of course, ideally dates should never be stored/entered as text anyway. The exception to this is if you have made a concious decision to always store them as text in a particular format, and not to use any date variables or functions.

[leinad31]

Boy, I missed that. Still a little confusing. "Database(mdb file)". I thought the OP had produced or purchased some non-VB software that would automatically include the date in the system date format. So he wanted to produce a program to warn the users to change the system date format. (A crazy concept, but that is what it seemed like).

If the OP was actually writing the date from a VB program, the solution is so simple as to be ridiculous. Just use Month(Date), Day(Date), Year(Date). That's all. Who cares what the system date format is?

Mac

See for yourself if your solution works. Here's a date 01/02/07. Are you 100% certain that the value returned by your functions will be the correct interpretation? It could be Jan 2 or Feb 1.

[Mr.Mac]

See for yourself if your solution works. Here's a date 01/02/07. Are you 100% certain that the value returned by your functions will be the correct interpretation? It could be Jan 2 or Feb 1.

Not sure what you are saying.

I just sat my Date format via Regional Settings to "dddd ddd" as an example bogus format.

I sat my computer date to January 2, 2007 and ran the program below and got the result
Tuesday Tue
2007-01-02

I then sat my computer date to Febrary 1, 2007 and got
Thursday Thu
2007-02-01

In other words, what I said about Month/Day/Year is true regardless.

Now maybe you are saying the date comes from some other source. Then, of course, don't use the Month/Day/Year functions. Instead, rely on the user to provide the date in the agreed format.

To the prompt
zDate = inputbox("Enter your birth date as mm/dd/yyyy")
I would expect the user to do that.

So, bottom line, the programs and techniques we provided to return the Regional Settings date format were a waste of time. All that is needed are the Month/Day/Year functions or just common sense.

Mac

Option Explicit

Private Sub Command1_Click()
MsgBox MyDate, , Date
End Sub

Private Function MyDate() As String
' Returns yyyy-mm-dd (today's date)
Dim w As String, d As String
d = Str$(Year(Date)) + "-"
w = LTrim$(Str$(Month(Date)))
If Len(w) = 1 Then d = d + "0" + w + "-" Else d = d + w + "-"
w = LTrim$(Str$(Day(Date)))
If Len(w) = 1 Then MyDate = d + "0" + w Else MyDate = d + w
End Function

[leinad31]

Not sure what you are saying.

I just sat my Date format via Regional Settings to "dddd ddd" as an example bogus format.

I sat my computer date to January 2, 2007 and ran the program below and got the result
Tuesday Tue
2007-01-02

I then sat my computer date to Febrary 1, 2007 and got
Thursday Thu
2007-02-01

In other words, what I said about Month/Day/Year is true regardless.

Now maybe you are saying the date comes from some other source. Then, of course, don't use the Month/Day/Year functions. Instead, rely on the user to provide the date in the agreed format.

To the prompt
zDate = inputbox("Enter your birth date as mm/dd/yyyy")
I would expect the user to do that.

So, bottom line, the programs and techniques we provided to return the Regional Settings date format were a waste of time. All that is needed are the Month/Day/Year functions or just common sense.

Mac

Option Explicit

Private Sub Command1_Click()
MsgBox MyDate, , Date
End Sub

Private Function MyDate() As String
' Returns yyyy-mm-dd (today's date)
Dim w As String, d As String
d = Str$(Year(Date)) + "-"
w = LTrim$(Str$(Month(Date)))
If Len(w) = 1 Then d = d + "0" + w + "-" Else d = d + w + "-"
w = LTrim$(Str$(Day(Date)))
If Len(w) = 1 Then MyDate = d + "0" + w Else MyDate = d + w
End Function

Your tests are biased. And it was already mentioned that there are no problems with date to string conversions, its the reverse that's problem riddled.

To the prompt
zDate = inputbox("Enter your birth date as mm/dd/yyyy")
I would expect the user to do that.

The point is, the user could have entered the format requested but the string would still be interpreted incorrectly if the PC short date setting was dd mm yyyy and implicit conversion or date functions are used.

[Mr.Mac]

The point is, the user could have entered the format requested but the string would still be interpreted incorrectly if the PC short date setting was dd mm yyyy and implicit conversion or date functions are used.

I should have said that if I am asking the user for a date in a certain format, I would input into a string variable and parse that variable accordingly.

If I ask for mm-dd-yyyy then I would edit what the user input. It should have 01-12 in left$(x$,2), a dash in mid$(x$,3,1), etc.

I wouldn't care about or mess with system dates. Same as if as asked for his height in format ff'ii'' or whatever.

Mac

[leinad31]

I should have said that if I am asking the user for a date in a certain format, I would input into a string variable and parse that variable accordingly.

If I ask for mm-dd-yyyy then I would edit what the user input. It should have 01-12 in left$(x$,2), a dash in mid$(x$,3,1), etc.

I wouldn't care about or mess with system dates. Same as if as asked for his height in format ff'ii'' or whatever.

Mac

No you said it was ridiculously simple, use Year(), Month() and Day() functions, you never said anything about parsing. Or about building dates with DateSerial(), you relied on implicit conversion. Si_the_geek and I wouldn't have bothered to correct or clarify a statement that was clearly already correct.

[Mr.Mac]

Sir,
I want to know the code to retrieve the system date format, whether it is dd/MM/yyyy or MM/dd/yyyy or else.

Well, devmaneesh, you have been provided with such code so that if someone has set the system date format to "mmm yy dddd" or even just "d", you can find that out. But you probably don't want that. You just want to know the components of a date.

1) Do this if the date you are interested in is today's date: Use the VB built-in functions MONTH(date), DAY(date), and YEAR(date). These will invariably give you the correct values regardless of the system date format, which you can safely ignore.

2) Do this if the date you are interested in is provided by the user (for example, a birth date): The date so provided should be in a STRING variable. Simply parse that string according to the agreed rule that the user was supposed to follow in providing the date.

3) Do this if the date you are interested in is provided by a database: If the database field format is DATE, use method 1 else use method 2.

If this answers your question, please mark this thread as resolved.

Mac

P.S. If you were happy with one of the solutions that provided the system date format because you actually needed it for some reason, please let us know why that was superior to MONTH(date), etc. Really curious.

[oceanboy]

Hi all,

I have similar problem here. I am asking the user to key in the date but because different user will have different date setting, I am trying to write something in the code that no matter if your setting is dd/mm/yy or mm/dd/yy, the code will convert it mm/dd/yy for the process. I thought this is simply as the matter of using format but it didn't work.

Can any one here give me some suggestion?

dim inputdate as string
dim datetime as date

inputdate = InputBox("Please enter the date of which you would like to restore", "Date & Time", "mm/dd/yyyy")

If inputdate = "" Or IsDate(inputdate) = False Then
    MsgBox ("Please enter the date of which you would like to restore")
    Exit Sub
End If

datetime = Format(inputdate, "mm/dd/yy")

the datetime stays the same after i use the format(). It seems that format() only works if i do msgbox(format(inputdate, "mm/dd/yy"))

Thanks!

ps: or perhaps I should use datetime as string instead of dateime as date? Then I won't even need the input date?...

[leinad31]

Hi all,

I have similar problem here. I am asking the user to key in the date but because different user will have different date setting, I am trying to write something in the code that no matter if your setting is dd/mm/yy or mm/dd/yy, the code will convert it mm/dd/yy for the process. I thought this is simply as the matter of using format but it didn't work.

Can any one here give me some suggestion?

dim inputdate as string
dim datetime as date

inputdate = InputBox("Please enter the date of which you would like to restore", "Date & Time", "mm/dd/yyyy")

If inputdate = "" Or IsDate(inputdate) = False Then
    MsgBox ("Please enter the date of which you would like to restore")
    Exit Sub
End If

datetime = Format(inputdate, "mm/dd/yy")

the datetime stays the same after i use the format(). It seems that format() only works if i do msgbox(format(inputdate, "mm/dd/yy"))

Thanks!

ps: or perhaps I should use datetime as string instead of dateime as date? Then I won't even need the input date?...

Avoid dates in string as much as possible. You misunderstood the gist of the thread. You can use a datepicker control instead of having the user input the date as text.

[oceanboy]

Is this control available in Access?

Is Calendar Control the same?

Thanks!

[leinad31]

If your gonna use a textbox and you know the format expected of the user then parse the string as explained several posts above. eg. if format is mm/dd/yyyy then use array=Split(string, "/") and assign value to datetime variable with DateSerial(array(2), array(0), array(1)).

[oceanboy]

hey Leinad31, I won't know what's the date setting on the user's computer but whether they key in dd/mm/yy or mm/dd/yy, i will always want the date to be in mm/dd/yy format.

I have tried using the array = split(string,"/") but I am not sure whether i have done it the way I intend to.

[si_the_geek]

If you don't know which format they have entered it, there is no way to safely convert it.

Instead do as leinad31 suggested, and use a control that inputs a date rather than text. In VB you can use the DateTimePicker or MonthView controls, and I think the Calendar control is valid on VBA forms within Office programs (but not in VB).

If you can't use any date specific controls, use separate controls for each part of the date (eg: one textbox for the month, on for the year, etc).