Geek asks and answers

T-SQL formatting calculated column as time

You can use the help of a guru on this. Trying to calculate the time between two time and time values ​​and show how time is in a T-SQL query ...

SELECT arrivalDate - departDate AS timeToComplete

This should be at least 24 hours. But who knows what the user can enter?

I tried something like this without any repetitions.

SELECT  
   CAST(time(7), 
   CONVERT(datetime, arrivalDate - departDate) AS timeToComplete) AS newTime,

Instead of showing results as 1:23:41an example, is there a way to show results such as:

0D, 1H, 23M, 33S.

Thanks for any advice on this.

+5
source share
3 answers

Just be different :) Try using this approach:

declare @ date1 datetime; declare @ date2 datetime;

set @date1 = '2012-05-01 12:00:000'
set @date2 = '2012-05-01 18:00:000'


    SELECT 
    STUFF(
        STUFF(
            STUFF(
                RIGHT(CONVERT(NVARCHAR(19), CONVERT(DATETIME, DATEADD(second, DATEDIFF(S, @date1, @date2), '20000101')), 120), 11), 
                3, 1, 'D, '), 
            8, 1, 'H, '), 
        13, 1, 'M, ') + ' S';
+2
source

, . I.e., , , .

DECLARE @arrivalDate DATETIME = '2013-01-19 23:59:59'
DECLARE @departDate DATETIME = '2013-01-25 11:52:30'

DECLARE @SecondsDifference INT = DATEDIFF(SECOND, @arrivalDate, @departDate)

DECLARE @DayDifference INT = @SecondsDifference / 86400
DECLARE @HourDifference INT = (@SecondsDifference - (@DayDifference * 86400)) / 3600
DECLARE @MinDifference INT = (@SecondsDifference - (@DayDifference * 86400) - (@HourDifference * 3600)) / 60
DECLARE @SecDifference INT = (@SecondsDifference - (@DayDifference * 86400) - (@HourDifference * 3600) - (@MinDifference * 60))

, , . DATEDIFF , , . :

DATEDIFF(HOUR, @arrivalDate, @departDate)

, .

+4

Finally I found a great solution on this link, SQL - seconds, day, minute, second thanks for the help, although people, this led me to this problem and finding the right information.

+1
source

More articles:


All Articles