SQL Server Date and Time Functions
SQL Server date and time functions are scalar functions that perform an operation on a date and time input value and returns either a string, numeric, or date and time value. Some date and time functions are categorized as deterministic functions while others are non-deterministic.
DATEADD Function
Date math is an important part of working with SQL. Using the DATEADD function you can add or subtract a datepart (month, day, year, week, hour, minute, second) from a date.
Syntax : DATEADD ( datepart , number , date )
Useful DateTime Functions
SELECT GETDATE() as Functions ,'Today' as Description
union ALL
SELECT DATEADD(d,-1,GETDATE()) ,'Yesterday'
UNION ALL
SELECT DATEADD(DAY, 10, GETDATE()), '10 Days Later'
UNION ALL
SELECT DATEADD(DAY, -10, GETDATE()), '10 Days Earlier'
UNION ALL
SELECT DATEADD(MONTH, 1, GETDATE()), 'Next Month'
UNION ALL
SELECT DATEADD(MONTH, -1, GETDATE()), 'Previous Month'
UNION ALL
SELECT DATEADD(YEAR, 1, GETDATE()), 'Next Year'
UNION ALL
SELECT DATEADD(YEAR, -1, GETDATE()), 'Previous Year'
Day
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) as Functions, 'First Day of Current Week' as Description
union ALL
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6), 'Last Day of Current Week'
union ALL
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ,'First Day of Last Week'
union ALL
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ,'Last Day of Last Week'
Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) as Functions ,'First Day of Current Month'as Description
union ALL
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ,'Last Day of Current Month'
union ALL
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ,'First Day of Last Month'
union ALL
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))), 'Last Day of Last Month'
Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) as Functions,'First Day of Current Year'as Description
union ALL
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) ,'Last Day of Current Year'
union ALL
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ,'First Day of Last Year'
union ALL
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))),'Last Day of Last Year'
Post a Comment