Universal date format for WHERE clauses.

Question

Universal date format for WHERE clauses.

MikeAngelastro-571287

SSCommitted

Points: 1576
More actions
January 27, 2010 at 12:52 pm

#137617

Hi,
I'm doing VB development that uses SQL Server and it must work regardless of location on earth. I was advised to use the following CONVERT statement for Aug 16, 2010.
SELECT * FROM Table WHERE UpdateDate < CONVERT(DATETIME, '20100816' , 113)
Now I'm not so sure this will work anywhere on earth. On the msdn web site it says that 113 is for Europe. In my VB code I create SQL statements that use WHERE clauses for dates. Because SQL is basically text, it is susceptible to date formatting complications when using a WHERE clause. I'd like to create the SQL statement so that it doesn't matter where the user is geographically.
I believe that there are two possible date format settings that may affect results here: the machine's and SQL Server's. But assuming that all is set up appropriately for the country, is there a way to achieve what I want?
Thanks,
Mike

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If you can get the date in the string in the format YYYYMMDD, SQL Server will always be able to correctly convert that date regardless of the settings of the server on which it is run.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

There are two datetime string formats that are interpreted correctly with with any language setting.

With the first format, only the YYYYMMDD part is required, but with the second format (ISO8601), you must supply everything except for the milliseconds.

20100127 15:33:13.343

2010-01-27T15:33:13.343

WayneS SSC Guru Points: 95460 More actions · Answer 3

Michael Valentine Jones (1/27/2010)
There are two datetime string formats that are interpreted correctly with with any language setting.
With the first format, only the YYYYMMDD part is required, but with the second format (ISO8601), you must supply everything except for the milliseconds.
20100127 15:33:13.343
2010-01-27T15:33:13.343

Since the OP stated it must work anywhere on earth, I'd like to point out that I don't think the second format will work with versions of sql prior to 2005... just in case that somewhere on earth is using 2000....

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MikeAngelastro-571287 SSCommitted Points: 1576 More actions · Answer 4

Thanks for all the quick responses!

Given them, do I need to make any change to my SQL statement above? If I want to include the time, what should I do differently?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

WayneS (1/27/2010)
Michael Valentine Jones (1/27/2010)
There are two datetime string formats that are interpreted correctly with with any language setting.
With the first format, only the YYYYMMDD part is required, but with the second format (ISO8601), you must supply everything except for the milliseconds.
20100127 15:33:13.343
2010-01-27T15:33:13.343
Since the OP stated it must work anywhere on earth, I'd like to point out that I don't think the second format will work with versions of sql prior to 2005... just in case that somewhere on earth is using 2000....

Works fine in SQL 2000, but doesn't work in SQL 7.0 or before.

set dateformat ydm

select Format_A=convert(datetime,'2010-01-27')

go

set dateformat ydm

select Format_B= convert(datetime,'2010-01-27T15:33:13.343')

Result in SQL 2000:

Server: Msg 242, Level 16, State 3, Line 3

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Format_B

------------------------------------------------------

2010-01-27 15:33:13.343

(1 row(s) affected)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Michael Valentine Jones (1/27/2010)
WayneS (1/27/2010)
Michael Valentine Jones (1/27/2010)
There are two datetime string formats that are interpreted correctly with with any language setting.
With the first format, only the YYYYMMDD part is required, but with the second format (ISO8601), you must supply everything except for the milliseconds.
20100127 15:33:13.343
2010-01-27T15:33:13.343
Since the OP stated it must work anywhere on earth, I'd like to point out that I don't think the second format will work with versions of sql prior to 2005... just in case that somewhere on earth is using 2000....
Works fine in SQL 2000, but doesn't work in SQL 7.0 or before.
set dateformat ydm
select Format_A=convert(datetime,'2010-01-27')
go
set dateformat ydm
select Format_B= convert(datetime,'2010-01-27T15:33:13.343')
Result in SQL 2000:
Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Format_B
------------------------------------------------------
2010-01-27 15:33:13.343
(1 row(s) affected)

Shouldn't the dashes be removed from this one?

set dateformat ydm

select Format_A=convert(datetime,'2010-01-27')

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Lynn Pettis (1/27/2010)
Michael Valentine Jones (1/27/2010)
WayneS (1/27/2010)
Michael Valentine Jones (1/27/2010)
There are two datetime string formats that are interpreted correctly with with any language setting.
With the first format, only the YYYYMMDD part is required, but with the second format (ISO8601), you must supply everything except for the milliseconds.
20100127 15:33:13.343
2010-01-27T15:33:13.343
Since the OP stated it must work anywhere on earth, I'd like to point out that I don't think the second format will work with versions of sql prior to 2005... just in case that somewhere on earth is using 2000....
Works fine in SQL 2000, but doesn't work in SQL 7.0 or before.
set dateformat ydm
select Format_A=convert(datetime,'2010-01-27')
go
set dateformat ydm
select Format_B= convert(datetime,'2010-01-27T15:33:13.343')
Result in SQL 2000:
Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Format_B
------------------------------------------------------
2010-01-27 15:33:13.343
(1 row(s) affected)
Shouldn't the dashes be removed from this one?
set dateformat ydm
select Format_A=convert(datetime,'2010-01-27')

No, my intention was to demonstrate that this format was not universal, 2010-01-27, and that the ISO8601 format is universal.

Jeffrey Williams SSC Guru Points: 90301 More actions · Answer 8

It sounds like you are building up a SQL string in your code using concatenation, and then submitting that to SQL Server to be executed.

Please be aware that technique is highly prone to SQL injection and also presents the kinds of problems you are facing now.

Instead, you should build up your SQL command using parameters - add the parameters to your command with the appropriate data type, then populate those parameters. If you follow this pattern, it won't matter what format the date is in - as it will be passed to SQL Server correctly.

This article has an example (about half way down) using VB.Net: http://www.sommarskog.se/dynamic_sql.html

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

MikeAngelastro-571287 SSCommitted Points: 1576 More actions · Answer 9

Jeffery,

Thanks for your comment. I tried to navigate to the url you provided, but my anti-virus program (Kaspersky) detected three trojans and thus blocked it. Is there another url I can navigate to for the information?

What I am doing in VB is using a function I created called DateFormattedUniversal(DateIn As Date) as String. The idea is that the string returned is the CONVERT statement. It is this CONVERT statement that is appended to the SQL statement as part of its WHERE clause.

Thanks,

Mike

Jeffrey Williams SSC Guru Points: 90301 More actions · Answer 10

Sorry - don't have another URL and not sure why your virus scanner is seeing something that isn't there.

Here is an example from that site, which shows how to create a parameter, define the data type and set it's value. Using this, you don't need to convert the date or worry about the format of the date string. You pass an actual date to the procedure and it passed correctly to SQL Server.

Set cmd = CreateObject("ADODB.Command")

Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText

cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _

" FROM dbo.Orders WHERE 1 = 1 "

If custid <> "" Then

cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "

cmd.Parameters.Append

cmd.CreateParameter("@custid", adWChar, adParamInput, 5, custid)

End If

If shipname <> "" Then

cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "

cmd.Parameters.Append cmd.CreateParameter("@shipname", _

adVarWChar, adParamInput, 40, shipname)

End If

Set rs = cmd.Execute

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

MikeAngelastro-571287 SSCommitted Points: 1576 More actions · Answer 11

Jeff,

Thanks again. I'm not using ADO .NET. I'm creating add-ons for an SAP program using their SDK. When I send a SQL statement to get a roecordset, I use their DIAPI. Although I can use ADO .NET, it is discouraged because it bypasses SAP's validation process.

When the user selects a date on a form, SAP validates it. If it is not a date, SAP pops an error message. Also the date coming into the function could merely be one that is returned by the VB today function. In addition, if the parameter going into the function is not a legitimate date, an exception is thrown. So it looks like the function I'm creating has no risk of causing SQL injection. I just want to be able to convert any legitimate date to a string so that the SQL statement that uses it will work anywhere on earth. Am I right in my assessment?

Thanks,

Mike