In a previous post, I showed you how to strip off some parts of a timestamp-field. In fact most of the objects used in the ‘ultimate generic date/time framework‘ are based on the use of the trunc-function.

But some people asked me for a MS-SQL Server version of the framework. To my annoyance, there is no trunc-function available in MS SQL Server!

So is there a workaround? You can do a lot with the cast-function. If you want to strip off the time from a timestamp, then you can write :

cast(current_timestamp As Date)

But the above solution only works for SQL Server 2008. And I also need to strip off other parts, like days, months,…

A combination of two functions dateadd() and datediff(), give us a beautiful solution. Let’s say I want to strip off the time part of a timestamp-field.

With the datediff() function, I can find how many days there are from day 0 to the current timestamp, and adding that many days back to day 0 with the dateadd() function, gives us the desired result.

dateadd(dd, datediff(dd,0, current_timestamp), 0)

It will work no matter how your datetime is stored and no matter what your locale is. In fact it’s complete platform independent.

But what if I want to create my Business Intelligence reporting based on a semester? Indeed, there is no semester part available in both functions!

But!

dateadd(mm, datediff(mm, 0, current_timestamp)-datediff(mm,0,current_timestamp)%6, 0)

And what about the above solution?  Try it out.


Advertisements