1

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://

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Could you show us which technique you are using to have NH query the DB – David Perlman May 03 '10 at 10:51
  • Are you using CreateQuery(), CreateCriteria(), CreateSqlQuery(), NHibernate.Linq or some other method? – David Perlman May 03 '10 at 11:33
  • I was wondering how you were passing the dates in. Have you tried setting the Thread.CurrentThread.CurrentCulture to the culture you are after. Could your app and sql server be on machines with different default cultures? – David Perlman May 03 '10 at 11:45
  • @ondesertverge: the SQL Server internal settings are independent of the OS – gbn May 03 '10 at 12:21

3 Answers3

4

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:

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
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • hmph - that does answer the sql part of the question but i have no idea how to use this in nhobernate :( –  May 03 '10 at 10:32
  • is there a locale or region setting, or can you format the date yourself? – gbn May 03 '10 at 10:36
  • 2
    seems there was the datetime2 type added to nhibernate to go with the MsSqlServer2008 dialect - going to try this... –  May 03 '10 at 10:48
0

You could try:

select * from table 
where Convert(DateTime, table.DateUpdated,103) >= Convert(DateTime, '2010-05-03 08:31:13:000',103)
codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • hey - cheers - i'm looking at that now - problem is this is coming from nhibernate. I don't understand why it doesn't work as striaght sql. –  May 03 '10 at 09:44
  • also - as this is getting a delta from when the app was last updated - it needs to be done using time as well as date... –  May 03 '10 at 09:45
0

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

what a PITA!!!