Friday, January 8, 2010

Some quick tips regarding datetime values

Until SQL 2008 we dont have a mechanism to store either date or time values alone. The only data type available is datetime which involves date as well as time part. Hence a common requirement that we face is strip off date part or time alone. Below represents some expressions to get this.

SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
will give us today's date after stripping of time part to 00:00:00

Similarly

SELECT DATEADD(dd,-1 * DATEDIFF(dd,0,GETDATE()),GETDATE())
will give us current time reducing date part to default date

These can be applied to get date/time part alone and then using format functions at front end you can display interested part alone.
(or use CONVERT in T-SQL which I don't recommend unless you don't have front end)

Some other similar date expressions

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
gives first day of current month

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
gives first day of current year

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
gives first day of current week