sql server datetime

Question

hey

i have the following query:

select * from table where table.DateUpdated >='2010-05-03 08:31:13:000'

all the rows in the table being queried have the following DateUpdated:

2010-05-03 08:04:50.000

it returns all of the rows in the table - even though it should return none.

I am pretty sure this is because of some crappy date/time regional thing.

if i swap the date to be

select * from table where table.DateUpdated >='2010-03-05 08:31:13:000'

then it does as it should.

How can i force everything to be using the same settings? this is doing my head in :)

This is sql generated by NHIbernate from my WCF service if that matters.

w://

Answer 1

You could try:

select * from table 
where Convert(DateTime, table.DateUpdated,103) >= Convert(DateTime, '2010-05-03 08:31:13:000',103)

Answer 2

Use this format "yyyymmdd hh:nn:ss.mmmm" which is locale independent in SQL Server, all versions

Somewhere, it's 5th Feb rather then 3rd May

Why:

• "yyyy-mm-dd" is not locale independent in SQL Server with datetime columns
• this anomaly is fixed with datetime2 in SQL Server 2008

References:

• Tibor Karaszi
• Tony Rogerson
• Me, on SO :-)

Example:

SET DATEFORMAT DMY  --UK
SELECT
    MONTH(CAST('2010-03-05 08:31:13:000' AS datetime)), --gives 5
    MONTH(CAST('20100305 08:31:13:000' AS datetime)) --gives 3


SET DATEFORMAT MDY  --default, USA
SELECT
    MONTH(CAST('2010-03-05 08:31:13:000' AS datetime)), --gives 3
    MONTH(CAST('20100305 08:31:13:000' AS datetime)) --gives 3

Answer 3

Could you show us which technique you are using to have NH query the DB

Answer 4

The answer to this was to upgrade to 2008 and use datetime2

what a PITA!!!