473,836 Members | 1,294 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Discussion: SQL Date Literals and Regional Settings

NeoPa
32,584 Recognized Expert Moderator MVP
** 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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 1 '11 #1
18 5106
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
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:
Expand|Select|Wrap|Line Numbers
  1. ? 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:
Expand|Select|Wrap|Line Numbers
  1. ? Isdate("11-30-2011") returns TRUE
  2. ? 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:
Expand|Select|Wrap|Line Numbers
  1. Public Function formatUSDate(dtInput As Date) As String
  2.     Dim strDate As String
  3.     formatUSDate = "#" & Month(dtInput) & "/" & Day(dtInput) & "/" & Year(dtInput) & "#"
  4. End Function
Dec 1 '11 #2
NeoPa
32,584 Recognized Expert Moderator MVP
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).
Dec 1 '11 #3
NeoPa
32,584 Recognized Expert Moderator MVP
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.) :
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 1 '11 #4
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
@NeoPa
That worked very nicely for me. Results shown below:
Expand|Select|Wrap|Line Numbers
  1. ? Format(Date, "\#m\/d\/yyyy\#")
  2. #12/1/2011#
  3. ? Format("30-11-2011", "\#m\/d\/yyyy\#")
  4. #11/30/2011#
  5. ? Format("11-30-2011", "\#m\/d\/yyyy\#")
  6. #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.
Expand|Select|Wrap|Line Numbers
  1. Public Function formatUSDate(dtInput As Date) As String
  2.     formatUSDate = Format(dtInput, "\#m\/d\/yyyy\#")
  3. 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.
Dec 1 '11 #5
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Dec 2 '11 #6
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Dec 2 '11 #7
patjones
931 Recognized Expert Contributor
I'm only kind of sort of following the discussion. I did the same thing and got the same result as you guys:

Expand|Select|Wrap|Line Numbers
  1. ? Format(Date, "\#m\/d\/yyyy\#")
  2. #12/2/2011#

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

Expand|Select|Wrap|Line Numbers
  1. ? Format(Date, "\#m/d/yyyy\#")
  2. #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:

Expand|Select|Wrap|Line Numbers
  1. ? Format("05/15/13", "\#m\/d\/yyyy\#")
  2. #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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. "'" & 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
Dec 2 '11 #8
NeoPa
32,584 Recognized Expert Moderator MVP
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 :-)
Dec 2 '11 #9
patjones
931 Recognized Expert Contributor
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.
Dec 2 '11 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
3355
by: David Lee AU | last post by:
We have an ASP.NET page that displays a date without a format specifier (in order to determine to default locale setting) and the date is shown as mm/dd/yyyy. We previously were able to set the locale to Australian values (incl date format of dd/mm/yyyy) using KB271587. I have recently installed the v1.1 of the .NET Framework and the format has reverted to the mm/dd/yyyy format. How can I set the locale settings to use the Austrlian locale...
1
4849
by: Laurence Neville | last post by:
This is regarding a change in the Short Date format under Hebrew Regional Settings, that has caused huge problems in our ASP web application. The change appears to have been introduced sometime before Windows 2000 Service Pack 4 and has remained through to Windows XP. I am looking for a solution that doesn't involve rewriting our application (much) and that allows all our users to keep using Hebrew Regional Settings. To summarize our...
4
15851
by: Jonathan | last post by:
Dear All, I am trying to set a webserver to use French regional settings for testing ASP pages. According to http://support.microsoft.com/kb/q306044, for IIS5, this is a matter of changing the regional settings for the authenticated user, and if this user does not have a user profile then the default regional settings. Unfortunately, this does not seem to be working in IIS6, in that I have
2
2415
by: isaacrajan | last post by:
Hello, Is there a way in which users can be prevented from making changes to regional settings in Win XP Professional edition so that the interpretation of dates by an Access application remains consistent for all users? Thanks Isaac
3
5257
by: Karunakararao | last post by:
Hi All, How can i get the date based on Regional settings . how can i get the date format using C# .NET " The date displayed in the date/time field should be formatted according to the regional settings made at the operating system level. For example, Canada uses a dd/mm/yyyy format while the US uses mm/dd/yyyy. The month and day should be padded with zeros. In other words, use 01/01/2005 rather than 1/1/2005. The date will...
12
9162
by: magister | last post by:
Hello, I know I can set the Culture to what I want, but shouldn't the current culture be taken from the regional settings on the web server's control panel!!! Mine is set to "united kingdom" but in my asp.net page I do
7
12647
by: Fred Flintstone | last post by:
I'm writing a VB.Net windows forms application. This line of code: Personal.EffectiveDate = GridRow2.Cells("New Value").Value.ToString.Trim Fails with this error: Cast from string "8/23/2005" to type 'Date' is not valid. The date being returned is in US English and my system is set for
1
1891
by: Lauren Quantrell | last post by:
I already figured out (the hard way) I need to convert all my date parameters into USA format before executing my stored procedures where dates are used as parameters. (Format(StartDate, "m/d/yyyy hh:nn:ss AM/PM") At least I thought I did! But then I discover that if I use the following construction, the dates do not need to be formatted to USA first:
0
1838
by: SharmaPunit | last post by:
I want to display a date in m/d/yy format. I have written the following lines of code and it is running perfectly till the date format in the regional settings of my computer is set to mm/dd/yyyy or any other which have '/' as a seperator. As soon as I change my date format in the control panel->regional settings-> date to yyyy-mm-dd i.e. with a seperator '-', the following lines of code returns date with '-' as a seperator(m-d-yyyy). I want...
3
2660
by: Abdul Qadir | last post by:
Hi, I am using Javascript for Excel Export from classic ASP. In my excel i have many Date columns that i need to store based on the regional settings. The application also allow us to set the dates in a format but this should be overridden with the regional Settings for the date. I have used toLocaleString() to convert the dates but for dd/mm/yyyy if i have some dates like 04/12/2010 its converting that to 12/4/2010 not happening for all but...
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9661
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10831
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10536
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10582
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10247
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7778
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.