Strange issue with 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

SQL Server Administration (2005)

Strange issue with dates

Author

Topic

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-24 : 07:39:30

I have just been asked about this and not seen it before. My regional settings are English (UK), but I think the server was installed originally under English (US).

When I run the following, I would expect the dates to both be the 9th July 2009:


declare @Date datetime
declare @Date2 datetime

set @Date = '2009-07-09 00:00:00'
set @Date2 = dateadd(dd,-77,getdate())
 
select @date as date, @date2 as date2
select DATEPART(month, @Date) m
select DATEPART(day, @Date) d
select DATEPART(month, @Date2) m2
select DATEPART(day, @Date2) d2

The problem is, I am getting the results as follows:


date                    date2
----------------------- -----------------------
2009-09-07 00:00:00.000 2009-07-09 12:36:32.557

(1 row(s) affected)

m
-----------
9

(1 row(s) affected)

d
-----------
7

(1 row(s) affected)

m2
-----------
7

(1 row(s) affected)

d2
-----------
9

(1 row(s) affected)

So getdate() is returning correctly, but setting the date myself (with any spacer) is swapping the day and month. Stranger is the fact that if I change it to '20090709' it works fine. Unsing convert gets ignored as well.

Can anybody shed any light on this?

Microsoft SQL Server 2005 - 9.00.4035.00 (X64)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 08:33:48

See what happens when you run this

declare @Date datetime
declare @Date2 datetime

set @Date = '2009-07-09T00:00:00'
set @Date2 = dateadd(dd,-77,getdate())

select @date as date, @date2 as date2
select DATEPART(month, @Date) m
select DATEPART(day, @Date) d
select DATEPART(month, @Date2) m2
select DATEPART(day, @Date2) d2

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-24 : 08:59:29

Thanks Madhi, Yeah, that works, unfortunately I can't get the users to do this. I wish I could, but apparently these things are sent to try us..

I just really need an explanation as to why this is happening so that I can then go back to them and tell them they HAVE to change the way they are doing things.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 09:09:32

quote:
Originally posted by RickD

Thanks Madhi, Yeah, that works, unfortunately I can't get the users to do this. I wish I could, but apparently these things are sent to try us..

I just really need an explanation as to why this is happening so that I can then go back to them and tell them they HAVE to change the way they are doing things.

Thanks

It is because as per ISO 8601, if dates are expressed in YYYY-MM-DD HH:MM:SS format, the only way to specify that it is language independent is to specify the Time part followed by a date using the letter T. If it is difficult to ask the users to do so, the only way is inputting dates in unambigious format YYYYMMDD HH:MM:SS

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-24 : 09:39:01

Thanks again, I still can not see why this is happening only on my 64 bit servers. It is working fine on the 32 bit servers we are running?!? Both are set to English (UK).

If I use a set dateformat, it works no problem as well. It is just when I set a variable without the set dateformat or a T.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-24 : 09:58:49

The initial date format for a connection depends on the language setting for the login, not the server’s language setting. If you need, you can change the setting for each login.

It is also possible that your application is modifying this setting or that an ODBC driver is modifying it.

You application should be designed to capture the date in the users desired format, and pass it to SQL Server in a datetime date format, or at least an unambiguous string format, like YYYYMMDD or YYYY-MM-DDTHH:MM:SS.

CODO ERGO SUM

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-24 : 11:06:19

No, its from Management Studio and the logins are all set to British English. As I said, it works fine on our 32 bit servers, but not on our 64 bit.

Unfortunately, most of the people here are using Management Studio to do their queries and calls (at least where this is a problem).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources