I am currently working an SQL script to calculate the difference between two dates which would give me the result in DD:HH:MI:SEC format. Example: Date 1: 7/30/12 4:00 PM Date 2: 5/4/12 10:31 AM
And the result should be 87:05:29:00
Can you kindly help with the script for this? Regards, Arjun
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year .
You can use either Day of year ("y") or Day of month ("m") to measure the number of days between date1 and date2 ("d"). DateDiff returns the number of weeks between the two dates when the interval is Weekday ("w").
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
If you are using sql-server then you can do this:
declare @x int,
@dt1 smalldatetime = '1996-03-25 03:24:16',
@dt2 smalldatetime = getdate()
set @x = datediff (s, @dt1, @dt2)
SELECT convert(varchar, @x / (60 * 60 * 24)) + ':'
+ convert(varchar, dateadd(s, @x, convert(datetime2, '0001-01-01')), 108)
Reference here
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With