Tuesday 13 December 2011

Best SQL Date Truncator

Let's say you want to capture or query the date but discard a portion of it:
Instead of 2011-12-13 01:05:05.154, just 2011-12-13 00:00:00.000 order 2011-12-01 00:00:00.000

The reasons you would need this? Perhaps you want to filter records only by day. Perhaps you're dealing with a database that doesn't use complete datetime in any records and you want <=today.

I can think of a number of ways to do it, but I can't decide which is best.
First of all - it's fairly easy if you want to simply eliminate the time portion. A number of tools will do that, but the most primitive, I think, would be cast-convert:

select cast(convert(char(10),getdate(),120) as datetime)
1. 2011-12-13 01:05:05:154 becomes a string in this format
2. the string gets chomped to the first 10 characters 2011-12-13
3. the remaining string 2011-12-13 casts to a datetime and because no further information is given, all other values default to 00:00:00.000

But is that the most efficient way of getting the job done? Is there any advantage to using a timestyle like 112 so you don't have to manually truncate?

The reason I tend to use the method above is that I can use it to find the Floor of a date at whatever level:
To find the first day of the month:
cast(convert(char(7),getdate(),120)+'-01' as datetime)
Or the first day of the year:
select cast(convert(char(4),getdate(),120)+'-01-01' as datetime)


Or any other day or day month or time or whatever - simply hack off what you don't want and replace it with what you do want.

Of course, you would have to be careful with capturing the 31st of months - that would be more of a datediff function and I'm thinking less efficient.

But what about creating a date out of capturing the year, month, dayofmonth separately and assembling a date. Is there any reason we should be using datepart rather than using the text functions?

No comments:

Post a Comment

Please provide your thoughts. I appreciate both flavours of feedback.