Showing posts with label truncate. Show all posts
Showing posts with label truncate. Show all posts

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?