/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Thursday, December 22, 2011

Using Datediff() Function in SQL SERVER

The DATEDIFF() function returns the time between two dates.
DATEDIFF(datepart,startdate,enddate)

Simple example using Datediff()

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS [Difference Date]

Calculating Intervals using Datediff() Function

USE test

CREATE TABLE [dbo].[MyTable](
    [CustomerId] [int] NULL,
    [DateTime] [datetime] NULL,
    [interval] [int] NULL
) ON [PRIMARY]

CREATE PROC CalculateInterval
AS
    DECLARE @InsertionTime DATETIME
    --SELECT @InsertionTime =  [datetime] FROM MyTable WHERE customerid=6
    SET @InsertionTime =  (SELECT TOP(1) datetime FROM MyTable ORDER BY datetime DESC)
BEGIN
    INSERT INTO MyTable(customerid,datetime,Interval)
    VALUES(7, GETDATE(), DATEDIFF(SECOND, @InsertionTime, GETDATE()))
END

EXEC CalculateInterval

Convert Seconds to hours:minutes:seconds

DECLARE @sec INT
SET @sec=7612
SELECT CONVERT(VARCHAR(5),@sec/3600)+':'+CONVERT(VARCHAR(5),@sec%3600/60)+':'+CONVERT(VARCHAR(5),(@sec%60))

Find total seconds of time in sql server. You can get the Total seconds of the current time in

SELECT [Total Seconds] =
(DATEPART(hh, GETDATE()) * 3600) +
(DATEPART(mi, GETDATE()) * 60) + DATEPART(ss, GETDATE())

References:
time (Transact-SQL) http://msdn.microsoft.com/en-us/library/bb677243.aspx
Date and Time Type
http://msdn.microsoft.com/en-us/library/ff848733.aspx 
DateDiff()
http://msdn.microsoft.com/en-us/library/aa258269%28v=sql.80%29.aspx
Date and Time Functions
 
datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns  

No comments:

Post a Comment

Recent Posts

My Blog List