2K Dates vs 2K5 Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

2K Dates vs 2K5 Dates

Author

Topic

dariomur
Starting Member

7 Posts

Posted - 2007-06-26 : 16:48:52

In Visual Studio, I assign the date to a variant like this:
vDate = Date 'response.writing this, it reads '26/06/2007'

I use it in SQL statements like this:
str = "SELECT * FROM Table WHERE UpdatedDate = '"& vDate &"'"
in SQL Server 2000, it works fine.

However in migrating to SQL Server 2005, I need to do this:
str = "SELECT * FROM Table WHERE UpdatedDate = CONVERT(DATETIME, '"& vDate &"', 103)"

SQL Server 2000 seems to accept American format and SQL Server 2005 seems to accept British format.
Is there anything I can do in my SQL Server/ASP/IIS settings to save me from rewriting every SQL Statement in my application?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-26 : 16:55:27

Are they on same server? What's date format at OS level?

dariomur
Starting Member

7 Posts

Posted - 2007-06-26 : 17:05:07

They are on different machines.
2K is on Server 2003 and 2K5 is on Vista Home Premium.
How does the OS affect it?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-26 : 17:05:22

Always use the universal date format:
YYYYMMDD HH:MM:SS.mmm, Example: 20071231 22:59:59.997

This is the only string format for a date this is universal for all language settings and settings of DATEFORMAT.

CODO ERGO SUM

dariomur
Starting Member

7 Posts

Posted - 2007-06-26 : 20:58:41

I really don't understand.

My question was: Is there anything I can do in my SQL Server/ASP/IIS settings to save me from rewriting every SQL Statement in my application?

Do I set this in the SQL Server properties? Did I mess up on the installation or are you saying I need to re-write the SQL Statements?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-26 : 23:19:56

Tried 'set dateformat'?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 01:42:23

1 Dont use concateneated sql instead use stored procedure with date parameter
2 No matter which format the client and Server have, use, as suggested YYYYMMDD format to express dates
3 www.sql-server-performance.com/fk_datetime.asp for more on dates

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 02:52:51

"are you saying I need to re-write the SQL Statements?"

Ideally yes. Wrap them in a Function that sorts them out, that will be useful for any future changes you decide to make - e.g. to prevent SQL Injection.

Kristen

dariomur
Starting Member

7 Posts

Posted - 2007-06-27 : 11:22:29

The SQL statements are in stored procedures, I've just paraphrased them here. I've read the great article above and I think that I understand your advice and I'm still puzzled.

I understand that the ideal statement is:
SELECT * FROM Table WHERE UpdatedDate = '2007-06-26'

What I don't understand is:
The 2000 server accepts:
SELECT * FROM Table WHERE UpdatedDate = '26/06/2007'
as valid and return the correct result.

... whereas the 2005 server, when executing the same query, returns:

Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type nvarchar to datetime.

... and the query needs to be changed to read:
SELECT * FROM Table WHERE UpdatedDate = '06/26/2007'
or to read:
SELECT * FROM Table WHERE UpdatedDate = CONVERT(DATETIME, '26/06/2007', 103)
(either option works)

Why?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 11:25:51

Run

dbcc useroptions

to know which format the server is set to

May be 2005 set to mdy thats why you are getting
As suggested if you use YYYYMMDD format, you wont get this error

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-27 : 12:04:00

quote:
Originally posted by dariomur

I really don't understand.

My question was: Is there anything I can do in my SQL Server/ASP/IIS settings to save me from rewriting every SQL Statement in my application?

Do I set this in the SQL Server properties? Did I mess up on the installation or are you saying I need to re-write the SQL Statements?

More than likely the 2000 and 2005 servers have different national language setting. My guess would be that the 2000 server is set to British English, and the 2005 server is set to US english.

However, I would say that where you messed up is when you wrote the stored procedures and didn't use an unambiguous date format.

As I said before, always use the universal date format:
YYYYMMDD HH:MM:SS.mmm, Example: 20071231 22:59:59.997

This is the only string format for a date this is universal for all language settings and settings of DATEFORMAT.

CODO ERGO SUM

dariomur
Starting Member

7 Posts

Posted - 2007-06-27 : 12:36:45

Thanks very much - I think I'm getting there.

I understand that the long term solution is to re-write my hundreds of SQL statements and thousands of line of asp code properly.

As a short term solution can I change the language without re-installing the server?

I've tried:
sp_configure 'default language', '23'
reconfigure
but it didn't take.

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 13:50:01

"I understand that the ideal statement is:
SELECT * FROM Table WHERE UpdatedDate = '2007-06-26'"

Note that in the ideal statement there are no hyphens in the string-date - just 'yyyymmdd' - the date is optional

There is an alternative format which requires the date and does have hyphens <sigh!>, and that is 'yyyy-mm-ddThh:mm:ss.mmm'

Kristen

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-06-27 : 15:50:29

dariomur,

You apparently missed this from rmaio:

quote:
Tried 'set dateformat'?

dariomur
Starting Member

7 Posts

Posted - 2007-06-27 : 18:37:31

It works!
I misunderstood that cryptic post - I thought that he wanted me to use "dateformat" in my SQL statements.

Full sentences can be SO useful sometimes :)

Thanks you all for your patience.

Anybody else that reads all of this, the lesson I learnt:

Always, always prepare your SQL statements in the format YYYYMMDD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources