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,…
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!
dateadd(mm, datediff(mm, 0, current_timestamp)-datediff(mm,0,current_timestamp)%6, 0)
And what about the above solution? Try it out.