DATEDIFF AND DATEADD FUNCTION

SQL DATEDIFF Function

Returns the number of date and time boundaries crossed between two dates

SQL DATEDIFF Syntax
DATEDIFF ( DatePart , StartDate , EndDate )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Output = 0 Year


SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate
Output = 1 quarter


SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate
Output = 2 Month


SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
Output = 61 day


SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
Output = 61 Day


SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate
Output = 9 Week


SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
Output = 1464 Hour


SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
Output = 87840 minute


SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
Output = 5270400 second


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Output = 86400000 millisecond

SQL DATEDIFF Function
SqlTutorials

Posted by Emil Chang at 6:18 AM 15 comments  

Labels: SQL DateADD and DateDiff, SQL Dynamic Get Last and First Day

Monday, June 4, 2007

SQL DATEADD Function

Returns a new datetime value based on adding an interval to the specified date.

SQL DATEADD Syntax
DATEADD ( datepart , number, date )


DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate
Output = 2010-06-04 00:00:00.000


SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Output = 2008-03-04 00:00:00.000


SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Output = 2007-09-04 00:00:00.000


SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Output = 2007-06-07 00:00:00.000


SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Output = 2007-06-07 00:00:00.000


SELECT DATEADD(Week, 3, @DateNow) AS NewDate
Output = 2007-06-25 00:00:00.000


SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Output = 2007-06-04 03:00:00.000


SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Output = 2007-06-04 00:03:00.000


SELECT DATEADD(second, 3, @DateNow) AS NewDate
Output = 2007-06-04 00:00:03.000


SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate
Output = 2007-06-04 00:00:00.003