Archive

Posts Tagged ‘datediff’

SQL Server – Calculating elapsed time from DATETIME

23.12.2013 8 comments

Elapsed time can be calculated from DATETIME field by extracting number of hours/minutes and seconds. You can use below query to calculate elapsed time between two dates:

-- Vishal - http://SqlAndMe.com

DECLARE @startTime DATETIME
DECLARE @endTime DATETIME

SET @startTime = '2013-11-05 12:20:35'
SET @endTime = '2013-11-10 01:22:30'

SELECT [DD:HH:MM:SS] =
CAST((DATEDIFF(HOUR, @startTime, @endTime) / 24) AS VARCHAR)
+ ':' +
CAST((DATEDIFF(HOUR, @startTime, @endTime) % 24) AS VARCHAR)
+ ':' +
CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime)
THEN CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR)
ELSE
CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60)
AS VARCHAR)
END
+ ':' + CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR),
[StringFormat] =
CAST((DATEDIFF(HOUR , @startTime, @endTime) / 24) AS VARCHAR) +
' Days ' +
CAST((DATEDIFF(HOUR , @startTime, @endTime) % 24) AS VARCHAR) +
' Hours ' +
CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime)
THEN CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR)
ELSE
CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60)
AS VARCHAR)
END +
' Minutes ' +
CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR) +
' Seconds '

Result Set:

DD:HH:MM:SS    StringFormat
4:13:2:55      4 Days 13 Hours 2 Minutes 55 Seconds

(1 row(s) affected)

 
[UPDATE] Earlier query had an error in calculation, thanks to Carlos for pointing it out and Nate for providing the correct solution.
 
Hope This Helps!

Vishal

How to get difference between two dates – TSQL

21.05.2011 1 comment

To calculate the difference between two dates, you can use DATEDIFF() function. The DATEDIFF() function returns the number of days/month/years and time between two dates.

Syntax:

DATEDIFF (date part, start date, end date)

 

For example, to calculate time left till Independence Day, you can use:

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE     @Today      DATETIME = GETDATE()

DECLARE     @IDay       DATETIME = '2011-08-15 08:30:00'

 

SELECT DATEDIFF(DAY, @Today, @IDay),      'Days Left'

UNION ALL

SELECT DATEDIFF(MONTH, @Today, @IDay),    'Months Left'

UNION ALL

SELECT DATEDIFF(YEAR, @Today, @IDay),     'Years Left'

UNION ALL

SELECT DATEDIFF(QUARTER, @Today, @IDay),  'Quarters Left'

UNION ALL

SELECT DATEDIFF(HOUR, @Today, @IDay),     'Hours Left'

UNION ALL

SELECT DATEDIFF(MINUTE, @Today, @IDay),   'Minutes Left'

UNION ALL

SELECT DATEDIFF(SECOND, @Today, @IDay),   'Seconds Left'

Result Set:

———– ————-

86          Days Left

3           Months Left

0           Years Left

1           Quarters Left

2052        Hours Left

123122      Minutes Left

7387278     Seconds Left

 

(7 row(s) affected)

To see all possible values for datepart check BOL.

 

Hope This Helps!

Vishal

How to get first Monday of Week/Month – TSQL

18.05.2011 3 comments

Following queries can be used to get First Monday of a week/month:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— Monday of Current Week

SELECT      DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0),

            'Monday of Current Week'

UNION ALL

— First Monday of the Month:

SELECT      DATEADD(WEEK, DATEDIFF(WEEK, 0,

            DATEADD(DAY, 6 DATEPART(DAY, GETDATE()), GETDATE())), 0),

            'First Monday of Current Month'

Result Set:

———————– —————————–

2011-05-16 00:00:00.000 Monday of Current Week

2011-05-02 00:00:00.000 First Monday of Current Month

 

(2 row(s) affected)

You can get the Start of Day and End of Day using below queries:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— Living in Today…

SELECT      DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0),

            'Start of Day'

UNION ALL

SELECT      GETDATE(), 'Now'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3,

            DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)),

            'End of Day'

Result set will be:

———————– ————

2011-05-18 00:00:00.000 Start of Day

2011-05-18 21:09:14.887 Now

2011-05-18 23:59:59.997 End of Day

 

(3 row(s) affected)

 

Hope This Helps!

Vishal

How to get First and Last day of a Year – TSQL

17.05.2011 3 comments

Yesterday, I posted bout How to get the First and Last day of a Month. Same can be modified to calculate first/last day of a given year:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— First/Last Day of the Year:

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR, 1, GETDATE())), 0),

            'First Day of Previous Year'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3, DATEADD(YEAR,

            DATEDIFF(YEAR, 0, DATEADD(YEAR, 1, GETDATE())) + 1, 0)),

            'Last Day of Previous Year'

UNION ALL

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0),

            'First Day of Current Year'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3,

            DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)),

            'Last Day of Current Year'

UNION ALL

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR,1,GETDATE())), 0),

            'First Day of Next Year'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3,

            DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR, 1, GETDATE())) + 1, 0)),

            'Last Day of Next Year'

Result Set:

———————– ————————–

2010-01-01 00:00:00.000 First Day of Previous Year

2010-12-31 23:59:59.997 Last Day of Previous Year

2011-01-01 00:00:00.000 First Day of Current Year

2011-12-31 23:59:59.997 Last Day of Current Year

2012-01-01 00:00:00.000 First Day of Next Year

2012-12-31 23:59:59.997 Last Day of Next Year

 

(6 row(s) affected)

These can be generalized as:

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @TRAVELYEARS INT = 1

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),

            'First Day of Year'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3,

            DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),

            'Last Day of Year'

 

SET @TRAVELYEARS = 1

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),

            'First Day of Year'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3,

            DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),

            'Last Day of Year'

Result Set:

———————– ———————-

2010-01-01 00:00:00.000 First Day of Next Year

2010-12-31 23:59:59.997 Last Day of Next Year

 

(2 row(s) affected)

 

                       

———————– ———————-

2012-01-01 00:00:00.000 First Day of Next Year

2012-12-31 23:59:59.997 Last Day of Next Year

 

(2 row(s) affected)

Hope This Helps!

Vishal

How to get First and Last day of a month – TSQL

16.05.2011 12 comments

Following queries can be used to get the first/last days of a month.

To get first day of a month use:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— First Day Previous/Current/Next Months

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) 1, 0),

            'First Day of Previous Month'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE())) 1),

            DATEADD(MONTH, 1, GETDATE())),

            'First Day of Previous Month (2)'

UNION ALL

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),

            'First Day of Current Month'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(GETDATE()) 1), GETDATE()),

            'First Day of Current Month (2)'

UNION ALL

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0),

            'First Day of Next Month'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE())) 1),

            DATEADD(MONTH, 1, GETDATE())),

            'First Day of Next Month (2)'

Result Set:

                       

———————– ——————————-

2011-04-01 00:00:00.000 First Day of Previous Month

2011-04-01 15:47:36.660 First Day of Previous Month (2)

2011-05-01 00:00:00.000 First Day of Current Month

2011-05-01 15:47:36.660 First Day of Current Month (2)

2011-06-01 00:00:00.000 First Day of Next Month

2011-06-01 15:47:36.660 First Day of Next Month (2)

 

(6 row(s) affected)

The above queries can be generalized as below:

DECLARE @DURATION INT = 2

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION, 0)

            AS '+2 Months'

 

SET @DURATION = 2

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION, 0)

            AS '-2 Months'

Result Set:

+2 Months

———————–

2011-07-01 00:00:00.000

 

(1 row(s) affected)

 

-2 Months

———————–

2011-03-01 00:00:00.000

 

(1 row(s) affected)

And, to get last day of a month use:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— Last Day Previous/Current/Next Months

SELECT      DATEADD(DAY, -(DAY(GETDATE())), GETDATE()),

            'Last Day of Previous Month'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3,

            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)),

            'Last Day of Previous Month (2)'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),

            DATEADD(MONTH, 1, GETDATE())),

            'Last Day of Current Month'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3,

            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)),

            'Last Day of Current Month (2)'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))),

            DATEADD(MONTH, 2, GETDATE())),

            'Last Day of Next Month'

UNION ALL

SELECT      DATEADD(SECOND, 1,

            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)),

            'Last Day of Next Month (2)'

Result Set:

                       

———————– ——————————

2011-04-30 15:54:35.523 Last Day of Previous Month

2011-04-30 23:59:59.997 Last Day of Previous Month (2)

2011-05-31 15:54:35.523 Last Day of Current Month

2011-05-31 23:59:59.997 Last Day of Current Month (2)

2011-06-30 15:54:35.523 Last Day of Next Month

2011-06-30 23:59:59.000 Last Day of Next Month (2)

 

(6 row(s) affected)

The above queries can be generalized as below:

DECLARE @DURATION INT = 2

SELECT   DATEADD(MILLISECOND, 3,

         DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION + 1, 0))

         AS '+2 Months'

 

SET @DURATION = 2

SELECT   DATEADD(MILLISECOND, 3,

         DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION + 1, 0))

         AS '-2 Months'

Result Set:

+2 Months

———————–

2011-07-31 23:59:59.997

 

(1 row(s) affected)

 

-2 Months

———————–

2011-03-31 23:59:59.997

 

(1 row(s) affected)

Hope This Helps!

Vishal