Date conversion fails on one machine only

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

General SQL Server Forums

New to SQL Server Programming

Date conversion fails on one machine only

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-07-18 : 11:44:26

When I run this basic script on my dev machine (Win 7 64, SQL 2008) it works perfectly:


DECLARE @d1 datetime
DECLARE @d2 datetime

SELECT
	@d1 = CAST('2010-06-10 23:00:00' as DATETIME),
	@d2 = CAST('2010-09-20 23:00:00' as DATETIME)

SELECT
	@d1,
	@d2

However, when the exact same code is run on a live server (Win 2003, SQL 2005) it fails with the following error:

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

The results show NULL for the second date, but the first date appears okay. Can anyone offer any suggestions as to why the second date is failing to convert?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 11:53:53

use convert and see

like

SELECT
@d1 = CONVERT(datetime,'2010-06-10 23:00:00',121),
@d2 = CONVERT(datetime,'2010-09-20 23:00:00',121)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 11:59:29

The problem is the default date format in the machines.
If you pass the date as one of
'2010-09-20T23:00:00'
'20100920 23:00:00'

then you won't have the problem
I think these are the only two formats that will be unambiguously converted.
Or use a convert style as visakh16 suggets

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-07-18 : 12:06:34

Okay great. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 12:08:10

quote:
Originally posted by R

Okay great. Thanks!

thats why i always suggest to use unambiguos format

see

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources