Subtracting Date Time in MSSQL

–YEAR
SELECT DATEDIFF(year, ‘2012-12-31’, ‘2013-01-01’)

–Quarter
SELECT DATEDIFF(quarter, ‘2012-01-01 23:59:59’, ‘2013-01-01 00:00:00’)

–Month
SELECT DATEDIFF(month, ‘2012-12-31 23:59:59.9999999’, ‘2013-02-01 00:00:00.0000000’)

–Day
SELECT DATEDIFF(day, ‘2012-12-01 23:59:59.9999999’, ‘2012-12-31 00:00:00.0000000’)

–Week
SELECT DATEDIFF(week, ‘2013-06-01 23:59:59.9999999’, ‘2013-06-30 00:00:00.0000000’)

–Hour
SELECT DATEDIFF(hour, ‘2013-06-30 08:30:00.0000000’, ‘2013-06-30 17:30:00.0000000’)

–Minute
SELECT DATEDIFF(minute, ‘2013-06-30 08:30:00.0000000’, ‘2013-06-30 17:30:00.0000000’)

–Second
SELECT DATEDIFF(second, ‘2013-06-30 08:30:00.0000000’, ‘2013-06-30 08:30:25.0000000’)

–Millisecond
SELECT DATEDIFF(millisecond, ‘2005-12-31 23:59:59.9999999’, ‘2006-01-01 00:00:00.0000000’);