+ CASE WHEN >= THEN AS VARCHAR(2)) + ' Minutes, ' ELSE '' END + CASE WHEN >= THEN AS VARCHAR(2)) + ' Hours, ' ELSE '' END + CASE WHEN >= THEN AS VARCHAR(3)) + ' Days, ' ELSE '' END , CASE WHEN >= THEN AS VARCHAR(4)) + ' Years, ' ELSE '' END , AS TotalSeconds - including this because it's handy for sorting results SELECT = ABS(DATEDIFF(ss, GETDATE()), GETDATE()))) SELECT = ABS(DATEDIFF(ss, GETUTCDATE()), GETUTCDATE()))) note: the ABS function is used to handle an end date that is before the start date SELECT * FROM Utility.udfDateDiff(' 00:00:00', ' 00:00:10', 1) -end date before start date SELECT * FROM Utility.udfDateDiff(' 00:00:00', NULL, 0) -time since Christmas 2010 not UTC SELECT * FROM Utility.udfDateDiff(' 00:00:00', NULL, 1) -time since Christmas 2010 in UTC SELECT * FROM Utility.udfDateDiff(NULL, NULL, 1) -NULL values If this is NULL, the current UTC date/time will be used. If this is NULL, the current UTC date/time will be => Optional. The start date of the timespan to compare. Returns properties describing the total amount of time between two => Optional.
It did inspire me to write this user defined function though.ĬREATE FUNCTION Utility.udfDateDiff BIT I agree that the result of that approach does not produce the correct result.
StartDate endDate Days Hours Minutes SecondsĮDIT: I based the routine more-or-less on Thilla's reply. Left(convert(binary(8), endDate),4))) as bigint) Right(convert(binary(8), startDate),4)))/300 as bigint) + cast(convert(integer, convert(binary(4), Left(convert(binary(8), startDate),4))) as bigint) I suspect that if you dig around this forum and the net that you will find a better routine, but here is yet another alternative:Ĭast((endSecond - startSecond) / 86400 as integer) as ,Ĭast(((endSecond - startSecond) % 86400) / 3600 as tinyint) as ,Ĭast(((endSecond - startSecond) % 3600) / 60 as tinyint) as ,Ĭast((endSecond - startSecond) % 60 as tinyint) as Ĩ6400 * cast(convert(integer, convert(binary(4), This select converts the days and seconds of each date into BIGINT datatypes to avoid the overflow error 2. This select uses a CROSS APPLY operator in this case I chose CROSS APPLY to suggest the possibility of converting this select into an inline table function. Below is an alternative that seems to work over the entire date domain. One of the problems of using DATEDIFF when dealing with either seconds or milliseconds is that this function will result in execution errors if the start and end dates span then entire domain of possible dates.
0 kommentar(er)
