Another day, Another job and SQL Tip...

Apologies for not posting for a while but moving to a new job can be hectic.  I have been trying to get my head around new databases and ran into some code that I felt I needed to talk about.  This has to do with truncating the time on a datetime field.  I've seen this in many flavors but this is the one I currently ran across.

CAST(LEFT(datefield, 11) AS DATETIME)

My personal favorite back in my early days of SQL is this.

CONVERT(varchar,datefield,101)

When I went to PASS for the first time one of my favorite speakers, Itzik Ben-Gan suggested a much better solution.  When we are treating the datefield as a string, we are converting it from a number which takes up time.  A better way to to keep it in numbers by using the DATEDIFF function like so.

DATEDIFF(d,datefield,GETDATE())

This keeps it as a number and you don't have to convert back and forth in order to do comparisons like so.

WHERE DATEDIFF(d,datefield,GETDATE()) = DATEDIFF(d,'2012-11-02,GETDATE())

Just a quick one today but I'll try to get back on the ball on studying for the next post.