CODEWITHSUNDEEP
c#sqlsql-serverdatetime
Thomas
Thomas

Reputation: 2984

Any reason why a sql converted date is converted wrongly?

I'm using the following code:

sqlcom.CommandText = "SELECT * FROM myTable"
                    + " WHERE CAST(myTime AS DATE) >= CAST(@mySTime AS DATE)"
                    + " AND CAST(myTime AS DATE) <= CAST(@myETime AS DATE)"
                    + "order by myTime ";

sqlcom.Parameters.AddWithValue("@mySTime", stime);
sqlcom.Parameters.AddWithValue("@myETime", etime);

stime and etime are both DateTime columns. The following is an abbreviation of the code that sets them:

sTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(-1);

eTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(-1).AddDays(1).AddMilliseconds(-1);

Which for example leads to:

sTime = '2015-10-19 00:00:00';
eTime = '2015-10-19 23:59:59';

when displayed in the debugger (stime and etime have a few other options how they could be set that is why the sql is dynamically taking them in but in this current case the above holds true).

Now when I run the above SQL I get everything even from the current day! BUT when I change AS DATE to AS DATETIME it works as intended that I just get the LAST day and nothing from today.

Now my question is: Is there any reason why the original sql/date comparison fails? (could it be because of that it is just the millisecond -1 that it rounds it up to the next day? OR is there any other reason there?)

Upvotes: 2

Views: 104

Answers (1)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

eTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(-1).AddDays(1).AddMilliseconds(-1);

  • Why are you using .AddDays(-1).AddDays(1). It seems useless.

  • .AddMilliseconds(-1)

Your data type is datetime. datetime has an accuracy of 3ms with increments of .000, 003 or .007 seconds. Therefore, any of these 3 values minus 1 (ms) is always rounded back to the original value: 

xxx.000 - .001 = .999 => rounded to .000
xxx.003 - .001 = .002 => rounded to .003    
xxx.007 - .001 = .006 => rounded to .007

This seems useless as well.

Round up

'2015-10-19 23:59:59' won't be rounded but '2015-10-19 23:59:59.999' will be round up to '2015-10-20 00:00:00.000' because 999 is surrounded by 997 and 000. 000 is the closest value.

<= 18-10-2015 23:59:59

You will miss any time above 23:59:59.000 and below of equal to 23:59:59.997

CAST(myTime AS DATE)

This will most likely prevent the usage of index on myTime. It should not be used.

It is fine to stick to datetime although datetime2 would be a better choice. If you are looking for value on a specific day, you must look for value between DAY at 00:00:00 and the next day at 00:00:00.

You can find a lot of useful information about date comparison on most of the answer here, including my own anwser: Why does my query search datetime not match?

Upvotes: 4

Related Questions