Why are Date$() and Date() returning two different formats?

All of a sudden (but I can't think of what changed on my system to cause this), Date$() returns 07-05-2005 (mm-dd-yyyy) and Date() returns 05-07-2005 (dd-mm-yyyy).  On my system, it should be (dd-mm-yyyy).  The two functions are supposed to be equivalent except that the Date$() function returns a string.  I've tried formatting the string different ways and it seems that Date$() actually is reading the real date wrong.  For example (just to prove my theory):

    Date$() returns "07-05-2005" but should return "05-07-2005"
    Format$(Date$(), "dd/mm/yyyy") returns "07/05/2005" but should return "05/07/2005"
    Format$(Date$(), "Short Date") returns "7/05/2005" but should return "5/07/2005"
    Format$(Date$(), "Long Date") returns "Saturday, 7 May 2005" but should return "Tuesday, 5 July 2005"
    Format$(Date$(), "yyyymmdd") returns "20050507" but should return "20050705"

Can anyone please tell me why this is and how do I fix it?

HOW I INVESTIGATED THIS:
I noticed that an Access VBA form was displaying its default date value as mm/dd/yyyy but it is supposed to display as dd/mm/yyyy (using functions Format$(Date$(), "dd/mm/yyyy").

I checked the underlying table (which has the same default value) and noticed that pre-existing data was in the correct format but the "new record" line had the default value in the wrong format.

I checked my regional settings and they looked fine.  I tried resetting them and restarting my machine but that didn't make any difference.  My current settings are:
    Standards and formats = "Australia"
    Short date = 5/07/2005
    Long date = Tuesday, 5 July 2005

On the Advanced tab, I tried changing Language for non-Unicode programs from "English (United States)" to "English (Australia)" and checking the "Apply all settings to the current user account and to the default user profile".  This caused me to restart the machine after which I checked my Access DB and saw the odd behaviour remains.

I decided to check both VB6.0 and C#.  VB6.0 obviously uses the same function as VBA so the errant behaviour was observed there.  C# seems to work correctly.
LVL 4
kenshawAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't know why Date$ does what you describe, but I never used Date$ anyhow:
All the VB functions with $ are only present for backward compatibility anyhow, so should be replaced with the new functions (no $ at the end)

use Format(Date(), "dd/mm/yyyy") will return "05/07/2005".

The problem with the syntax Format$(Date$(),...) is that Date$ returns a string (indicated by the $ sign) which will then be interpreted by he Format function as Date, obviously assuming the MM/DD/YYYY format, thus the error in the output.

CHeers
kenshawAuthor Commented:
hmm... thanks but this raises a few points:

- I agree that appending $ to variables are the old way of defining their datatype and should be avoided but I question whether this is true of functions as they actually return different datatypes.  The $ functions return strings whereas the functions with $ return variants so, if you subscribe to the idea that variants should be avoided except where required, then the $ functions are definitely better from that point of view.

- I have never seen them described in this manner nor have I seen recommendations that the $ functions not be used.  If anything, I have only seen recommendations they be used and I have done so for as long as I can remember since programming VB5.0 and VB6.0 (7 years).

- the Format function is not the guilty party as the errant behaviour can be seen without the Format function at all: just examining Date() and Date$() in the Immediate window shows this behaviour

- I'm prepared to bet my life that this is new behaviour.  Has anyone else noticed this?  Since posting this question, I've checked another machine that has VB6.0 in a Windows 98 environment and the behaviour is there too so that pretty much eliminates anything to do with my Regional Settings.  I think it's possible that it's a recent Microsoft patch or hotfix

Regards and thanks
kenshawAuthor Commented:
oops, I meant the functions WITHOUT $ return variants!
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

kenshawAuthor Commented:
I may have found an answer to my own question (even though this doesn't explain why I only now noticed the difference between Date$() and Date()).  In Microsoft Knowledge Base article on "ACC: How to Format Any Date As U.S. Date Regardless of Regional Settings", the following is metioned:

Note: For the current date and time only:

The Date$ function returns the current date in US format (mm-dd-yyyy) regardless of the regional settings. The Date function, however, returns a variant number, which when converted to a string, is formatted according to regional settings.

Similarly, the Time$ function returns the current time formatted as "hh:nn:ss" on a 24-hour clock regardless of regional settings. The Time function, however, returns a variant number, which when converted to a string is formatted according to regional settings.
kenshawAuthor Commented:
Guy Hengel [angelIII / a3]Billing EngineerCommented:
As I admited I didn't know which format Date$ would use to format the date value, but it processes as I tought.

I remember the $ thingy from VB4 and lower to indicate the variable type:
VB4  Dim mystring$  
VB5  Dim mystring as string
Actually, in VB5 you can also use the VB4 syntax, but is depreciated. Similar for the functions...

So, you should change all the Date$ into Date and it should work fine...    
PaulHewsCommented:
>even though this doesn't explain why I only now noticed the difference between Date$() and Date()

The reason you never noticed before is because VB generally does a pretty good job of hiding data type conversions.  :)  Even though Date$ function returns a string, it can be assigned to a date variable, and VB will do the conversion rather than give any protest.  The Date() function returns a proper date data type and should be used all the time for that purpose, as string to date conversions ARE susceptible to regional differences, and this may introduce subtle errors into your code.  



kenshawAuthor Commented:
What I meant was that I didn't notice the format change.  On the other hand, I've always been clear about the difference in datatype from these two functions (and datatype coercion in general in VB6.0).  Actually, the Date function doesn't return a Date data type.  It returns a Variant.  The difference in values is NOT because of the difference in datatypes - it is because the Date$ and Date functions actually deal with locale differently (one obviously completely ignores your regional settings) and this difference is either new or barely documented.  As far as I can tell, it's not in MSDN Library 6.0, the function definition (as viewed in the object browser, for example), MSDN online (except for the perfunctory allusion in the article I found, above), etc.  In summary, what I am saying is that the "problem" I first posted is due to a difference in the respective functions' functionality, not due to the subtleties of datatype coercion.
PaulHewsCommented:
>It returns a Variant.
Yes, but a Variant Date sub type...

>The difference in values is NOT because of the difference in datatypes - it is because the Date$ and Date functions actually deal with locale differently (one obviously completely ignores your regional settings) and this difference is either new or barely documented.

It is because of the implicit conversion from string to date when using Date$. There is no such conversion issue when converting variant date to date datatype.

But don't take my word, see for yourself:
Private Sub Command1_Click()
    MsgBox TypeName(Date)
    MsgBox TypeName(Date$)
End Sub




PaulHewsCommented:
If I can make it more clear, it isn't relevant to say that the Date() function respects locale settings, because the date is stored internally as a 8 byte number (in a variant wrapper.)  Any time you convert a string to a date, you have the possibility of locale problems.

My rules for date handling to help avoid string conversion errors:
-All variables that take a date, are of Date datatype.
-All output is done by formatted strings from a date variable.
-User input is best done with a date picker control. (Not converting from string.)
-When formatting a string for SQL queries, use yyyy/mm/dd format as this is an unambiguous format that the db will recognize.
-Date literals are delineated in VB with hash marks (#), not with double quotes.
kenshawAuthor Commented:
Thanks for the debate/discussion!  I agree with dimming variables as Date for dates, using international or yyyy/mm/dd format, etc and this is certainly the approach I have always taken.  I also know that Date$ returns a string and that Date is a variant containing a date (I prefer to check object browser rather than TypeName).  That's not my point.  My point is that, Date$ is not merely a conversion of Date to string.  Certainly, the values returned by Date$ and CStr(Date) are NOT equivalent.  If you have a non-US regional setting (that doesn't use mm/dd/yyyy), it's easy for you to try this.  This coupled with the fact that MSDN KB says that Date$ ignores regional settings whereas Date formatted to string obeys regional settings suggests that they are in fact different functions.
PaulHewsCommented:
None of the <function>$ functions are documented in the help, because they're all leftovers from previous versions of Basic.  For instance, the Date$ function goes back at *least* to GWBASIC 3.22 which I used to run on my 8086 IBM clone.  Small wonder that it ignores the windows locale when it returns the date string... It predates Windows by several years.  These are all poorly documented or undocumented language elements.

> If you have a non-US regional setting (that doesn't use mm/dd/yyyy), it's easy for you to try this.  This coupled with the fact that MSDN KB says that Date$ ignores regional settings whereas Date formatted to string obeys regional settings suggests that they are in fact different functions.<

True enough.  But if you follow the rules outlined above, there's never a necessity to use Date$.   Just treat it like the other deprecated parts of the language.   You can code without Gosub, Goto, variable type-declaration characters, etc.  Relating to the second comment of yours in the question where you say that "... variants should be avoided except where required"  I would say that the Date function is the exception where the variant function is preferred, for all the reasons stated in this discussion.
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.