January 27, 2010 at 12:52 pm
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
January 27, 2010 at 12:57 pm
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.
January 27, 2010 at 1:40 pm
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
January 27, 2010 at 2:09 pm
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.3432010-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
January 27, 2010 at 2:32 pm
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?
January 27, 2010 at 3:37 pm
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.3432010-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)
January 27, 2010 at 3:41 pm
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.3432010-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 3The 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')
January 27, 2010 at 3:50 pm
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.3432010-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 3The 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.
January 27, 2010 at 5:36 pm
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
January 27, 2010 at 6:05 pm
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
January 27, 2010 at 6:16 pm
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
January 28, 2010 at 8:59 am
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply