What are you looking for ?
Home » » DATEADD Function

DATEADD Function

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'
Share this article :

Post a Comment

Flag Counter

Social Profile

 
Copyright x 2011. By Wael Medhat - All Rights Reserved