473,836 Members | 1,364 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
18 5106
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Dec 2 '11 #11
patjones
931 Recognized Expert Contributor
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).
Dec 2 '11 #12
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Dec 2 '11 #13
patjones
931 Recognized Expert Contributor
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.
Dec 6 '11 #14
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Dec 6 '11 #15
tsgiannis
2 New Member
I don't know if it is a good Solution but due to dealing with a ton of related troubles i created this function...and yes i know it is slow
Expand|Select|Wrap|Line Numbers
  1. Public Function SQLDate(InputDate, Optional DebugState As Boolean) As String
  2. On Error Resume Next
  3. Dim Sday, SMonth, Syear, OutputString As String
  4. If Not IsDate(InputDate) Then Exit Function
  5. Syear = Format(Year(DateValue(InputDate)), "0000")
  6. SMonth = Format(Month(DateValue(InputDate)), "00")
  7. Sday = Format(Day(DateValue(InputDate)), "00")
  8. OutputString = Syear & "-" & SMonth & "-" & Sday
  9. If DebugState Then
  10. MsgBox "Input Value is : " & InputDate & vbCrLf & _
  11.         "Decoded Year is : " & Syear & vbCrLf & _
  12.         "Decoded Month is : " & SMonth & vbCrLf & _
  13.         "Decoded Day is : " & Sday & vbCrLf & _
  14.         " SQL will be feeded " & OutputString
  15. End If
  16. SQLDate = OutputString
  17. End Function
Dec 9 '11 #16
NeoPa
32,584 Recognized Expert Moderator MVP
I'm afraid that code doesn't really handle the situation too well. I suggest you read the linked article (Literal DateTimes and Their Delimiters (#)) for a better understanding of the various problems involved. The OutputString you should be expecting for Christmas Day is "#12/25/2011#" (Yours appears as "2011-12-25").
Dec 9 '11 #17
tsgiannis
2 New Member
if i remember correctly sql likes the "yyyy-mm-dd" format.....but this is something a read a long time ago and maybe i am wrong
Dec 13 '11 #18
NeoPa
32,584 Recognized Expert Moderator MVP
This is a very late reply.

You weren't wrong. I found yet another new aspect of SQL dates and their formatting. It was a while ago now, but I suspect your comment was the trigger for me looking again.

The linked thread should now include that and I actually use that format now as standard.
May 22 '16 #19

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
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...
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...
0
9367
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
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();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4005
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3108
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.