Discussion: SQL Date Literals and Regional Settings

[NeoPa]

** NB ** This thread was split away from a technical question (Help on trouble handling date.).

Dates in SQL don't use Regional Settings.

This is why we get this question (or similar) so many times here. It doesn't help that many 'solutions' given out by experts seem to ignore this. The main reason for this, of course, is that the SQL standard format (which, as I said, is independent of Region) is the same as the Regional format for the USA (IE. m/d/yyyy). This means that a large part of the world never sees the problems involved with writing dates into SQL (and filters also contain SQL instructions of course) just as they come. Access uses Regional Settings to display dates, so date literals need to be formatted explicitly to work reliably in SQL (Even outside of the USA dates are often interpreted correctly even though in the wrong format because the SQL engine recognises dates such as 22/11/2011 as really being 11/22/2011 because there are only twelve months in a year).

For more on this see Literal DateTimes and Their Delimiters (#).

PS. To format a date correctly you can always use the following :

Format([DateVal], "\#m/d/yyyy\#")

It is not always necessary to include the hashes (#) in there though, as these can easily be appended as part of the rest of the SQL string.

[TheSmileyCoder]

Sorry NeoPa.
Im afraid its a bit more complicated then that. You see, if I with my regional settings, and my settings for what charecter I want shown between the day, month and year is not compatible with the VBA/SQL engine trouble is just around the corner.

For example if I were to use the immediate pane:

? Format("2011-11-30","mm/dd/yyyy")

VBA returns: "11-30-2011"
Note that even though I specified slashes in the format command, VBA returns with dashes (as specified by my local computer settings).
But Mihail typing the same command is getting from VBA: "11.30.2011 ".

Now the difference between these two results, and especially how VBA treats the two becomes more obvious with a little test:

? Isdate("11-30-2011") returns TRUE
? Isdate("11.30.2011") returns FALSE

So VBA is simply not able to understand the value Mihail is getting as a date.

In his case, I presume the answer lies in using a custom function such as:

Public Function formatUSDate(dtInput As Date) As String
    Dim strDate As String
    formatUSDate = "#" & Month(dtInput) & "/" & Day(dtInput) & "/" & Year(dtInput) & "#"
End Function

[NeoPa]

Wow Smiley. That was a shock. All this time I thought I had that all well understood, but my experience has nearly all been about European countries which use slashes and the USA. Everywhere with slashes (/) even if the order changes. So even my 'portable' code is not 100% portable! That's a nasty blow I have to admit.

I will look at this as a matter of priority and see what I can come up with (and if I find anything you two can be my accomplices and test it out for me. That would be very helpful).

[NeoPa]

This is horrible. I think I may have found a viable solution, but I corrected someone recently for using it (I just explained it wasn't necessary really - but I was quite wrong it seems).

Anyway, try the following format for me if you would guys (The Immediate Pane should do, but if you have a SQL string to try it out within too that would be even better.) :

Format(Date, "\#m\/d\/yyyy\#")

Essentially, this escapes the slashes (/) so they are not recognised as such when it comes to the conversion for local settings, but leaves them as slashes in the same place in the resultant string.

PS. My grateful thanks Smiley, for pointing me so clearly in the right direction. It's bad enough being wrong, but leading others astray is intollerable and you've saved me from continuing with that.

[TheSmileyCoder]

@NeoPa
That worked very nicely for me. Results shown below:

? Format(Date, "\#m\/d\/yyyy\#")
#12/1/2011#
? Format("30-11-2011", "\#m\/d\/yyyy\#")
#11/30/2011#
? Format("11-30-2011", "\#m\/d\/yyyy\#")
#11/30/2011#

Running 100.000 iterations of your method versus mine, on dates formatted both as US and European reveals your method is 3 times faster then mine. That said 100.000 iterations still only took 470 ms with your method, so still worth looking it, should you be formatting 100k+ records.


I also find your method more gracefull then mine, allthough I will still encapsulate it into my FormatUsDate function, since I find it easier to look at that way.

Public Function formatUSDate(dtInput As Date) As String
    formatUSDate = Format(dtInput, "\#m\/d\/yyyy\#")
End Function

I also did not know about this issue, until investigating this thread in more detail, but after all, thats part of the reason I visit Bytes, to learn new stuff myself. While I do enjoy the "Feel good" of helping others I still firmly believe that I gain something myself as a programmer with each question I answer or when I look at what you and other experts answered, which is also the case for this thread. The thought of escaping the slashes in the format string is not something I would have thought of myself.

[NeoPa]

Thank you Smiley. That's very nice to hear. I think I may have to do some more work on that article now after all this. Never mind. It enhances the quality and that's all good.

NB. I would consider renaming your function to formatSQLDate() as there are already far too many people around who don't appreciate even that SQL has a set standard for formatting date literals. It is for this reason, rather than anything to do with USA, that it is formatted that way. It's just a cosmetic detail really, but worth considering I believe.

[NeoPa]

FYI: I've updated the article linked in the first post (Literal DateTimes and Their Delimiters (#)) to reflect this potential problem so it now includes instructions to handle it.

[patjones]

I'm only kind of sort of following the discussion. I did the same thing and got the same result as you guys:

? Format(Date, "\#m\/d\/yyyy\#")
#12/2/2011#

But I also did this, and got the same result:

? Format(Date, "\#m/d/yyyy\#")
#12/2/2011#

It makes sense to me that the backslashes in this case appear to escape the "#". This leads to ask though what the backslashes in the first version after the "m" and "d" respectively do.

In all of my projects over the years, I have consistently used mm/dd/yy format for storage in Access tables, display on forms and reports, and the input mask in data entry fields. It's just my preference and not a single user of any of my applications has questioned it. Now, if I pass one of my dates into Format( ) as above I get:

? Format("05/15/13", "\#m\/d\/yyyy\#")
#5/15/2013#

This is great. BUT, suppose now that I try to pass the following into an EXEC that calls a SQL Server stored procedure, which INSERTs the date into a table on the server:

Format(Me.fldExpirationDate, "\#m\/d\/yyyy\#")

Here fldExpirationDa teis just a text box on my form that is date formatted mm/dd/yy. This gives rise to an ODBC error. However, if I pass it in as a string (which has been my standard practice when EXECing from VBA), then it goes in fine:

"'" & Me.fldExpirationDate & "'"

On the SQL Server side, this results in a table entry that looks like '2013-05-15' or whatever (it's a DATETIME column). I don't particularly like treating dates as strings; but it's the only way I've been able to make it work so far. I don't know if I'm making any sense here. At this point I'm blabbing on about how confounding dates are and I'll just stop.

Pat

[NeoPa]

patjones:
It makes sense to me that the backslashes in this case appear to escape the "#". This leads to ask though what the backslashes in the first version after the "m" and "d" respectively do.

Pat, if you look at the linked article again now, you'll see a recently added section that explains that for you. I doubt it will impact your usual useage as your date format separator characters are already slashes (/). In fact your date formats are similar in all ways to the SQL format, so no problems will ever be noticed in any of your databases unless they are used abroad, and particularly in areas that don't use the slash character for dates.

patjones:
In all of my projects over the years, I have consistently used mm/dd/yy format for storage in Access tables

This is a flawed statement, as it presupposes that dates can be stored in the form of their format. This is not true at all. Dates are stored as Double Precision floating point numbers representing the number of days since 30 December 1899. This is lucky, because those of us that remember Y2K clearly (as opposed to most of the media who completely misunderstood why no planes fell from the sky), know how many problems result from 2 digit years when changing centuries.

patjones:
This is great. BUT, suppose now that I try to pass the following into an EXEC that calls a SQL Server stored procedure, which INSERTs the date into a table on the server:

SQL Server is exceptional in that it seems to pay no heed whatsoever to the ANSI-92 standards in this regard. In fact, I struggled exactly as you have done. I hated the fact that I could find no way of specifying a date/time literal within T-SQL and always had to resort to a function call that took a string parameter and converted it at run time. I'd be happy to hear that I'm mistaken on this point, but I was never able to find anything to explain why it's done the way it is in SQL Server. Thankfully, Access's Jet SQL does seem to conform to the standards in this respect.

Hopefully, these points will make your understanding of dates, and working with them, more solid in future :-)

[patjones]

NeoPa:
In fact your date formats are similar in all ways to the SQL format

That's what I don't understand. How is '2013-05-15' similar to '05/15/13'? They seem totally different to me.

NeoPa:
This is a flawed statement, as it presupposes that dates can be stored in the form of their format.

Yes, I did know that dates are stored as floating point numbers. I suppose I should have said that I always format dates to appear in mm/dd/yy form. I must try to remember not to get sloppy with my semantics.

[NeoPa]

patjones:
That's what I don't understand. How is '2013-05-15' similar to '05/15/13'? They seem totally different to me.

Everything I saw in your post indicated you were using mm/dd/yyyy as your format. I assumed that was your Regional Setting (Nothing indicated otherwise). I'll read it through again bearing this new information in mind and see if I can understand what it is you're stuck on.

[patjones]

My regional setting is set to that, but what I was referring to is that I always set the format of my text boxes, etc. to mm/dd/yy in order to force two digit display regardless of the day or month (e.g. 05/08/11 as opposed to 5/8/11 or 5/8/2011). (I understand that using a two-digit year may not be best practice, but I don't think it's a huge deal. That may be a topic for another thread).

[NeoPa]

patjones:

NeoPa:
In fact your date formats are similar in all ways to the SQL format

That's what I don't understand. How is '2013-05-15' similar to '05/15/13'? They seem totally different to me.

Now I'm really confused. So where does the '2013-05-15' format come into the question? I assumed (from the associated quote) that the former was your regional format and the latter was the SQL format (ignoring # of digits in years which isn't a specific part of the standard anyway). Now you say your regional format is the same as the SQL standard I'm wondering what the question is.

patjones:
My regional setting is set to that

Actually, your statement (included here) may not have meant what I read it to mean at all. The 'that' in your sentence could refer equally well to both the SQL format or the yyyy-mm-dd format. Why don't you explain and we can take it from there.

[patjones]

In post #9, you said that my "date formats are similar in all ways to the SQL format". Well, dates in my SQL Server tables always appear like yyyy-mm-dd. This is different from the regional format on my desktop machine, which is m/d/yyyy. It is also different from the mm/dd/yy format that I use for all of my Access work. In light of those observations, I was looking for clarification as to the above quoted statement that you made. It is not a crucial issue, as I have not yet encountered problems with SQL Server's date format; it is merely a curiosity on my part. Thanks.

[NeoPa]

That makes more sense Pat. I understand the confusion now. I should possibly have been clearer - The SQL format I was referring to has nothing to do with SQL Server or T-SQL at all (unfortunately) as it refers to the SQL-92 standard, which SQL Server doesn't seem to comply with, for reasons I've never discovered.

See post #9 for some clarification of these points.