开发者

sql server datetime

开发者 https://www.devze.com 2022-12-28 18:24 出处:网络
i have the following query: select * from table where table.DateUpdated >=\'2010-05-03 08:31:13:000\'

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 NHIberna开发者_运维问答te from my WCF service if that matters.

w://


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


You could try:

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


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

what a PITA!!!

0

精彩评论

暂无评论...
验证码 换一张
取 消