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!!!
精彩评论