Date Question in SQL Statement

[chuckh70]

I have the following sql statement wich works fine, but it returns the date as 12/20/2004 00:00:00

I need to strip the time off, but haven't been able to make this happen.

SELECT DATEADD(dd, Create_Time / 86400, '1/1/1970') AS 'Date Open', COUNT(*) AS tcktOpen FROM HPD_HelpDesk WHERE AND (Status >=4) AND (DATEADD(dd, Create_Time / 86400, '1/1/1970') >= '1/1/2004')AND (DATEADD(dd, Create_Time / 86400, '1/1/1970') < '12/20/2004') GROUP BY DATEADD(dd, Create_Time / 86400, '1/1/1970') Order By DATEADD(dd, Create_Time / 86400, '1/1/1970')ASC

 

[ESquared]

I'm not blown away at all. Number manipulation is always faster than string manipulation. Remember that permutations problem we worked on shortly after I first came to tek-tips? My program greatly sped up when I stopped using a string and moved the pieces around in number variables.

My guess for the use of '1900-01-01' is that it will mean nothing to the query engine because the expression has to be evaluated only once. A blink in the eye of the CPU. Of course, as long as you are consistent, you can use any date or number value you like. But that would be misleading for no reason.

 

[vongrunt]

0 vs 1900-01-01: no difference. Exec plans are identical, optimizer simply replaces one constant with another - only once.

 

[r937]

well, i will certainly buy into a concept that not only makes sense but is significantly faster

neither 0 nor 1900-01-01 has particular appeal, but i guess i could get used to either

nice job, guys

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

My vote is for 0. It's less typing, and forces the next person to learn about how datetime values are stored (meaning he or she is more likely to write queries correctly).

 

[ESquared]

One thing I've noticed since this thread is that the method is limited by the data size of int.

Try this on your server:

SELECT DateAdd(ms,DateDiff(ms, 0, '12/31/9999'),0)

But then, you can always choose a different reference value, if you want. It doesn't have to be 0. It just has to be the same in both parts of the expression.

-------------------------------------
Only the unvirtuous can be dutiful, for the virtuous already perform duty's requirements by inclination and for pleasure. Where there is no pain, no disinclination, there is no duty.
- Erik E