CODEWITHSUNDEEP
sql-server-2008-r2datepart
Ege Bayrak
Ege Bayrak

Reputation: 1199

How to concatenate datepart results?

I have a column called "Total" which holds values such as "1899-12-30 07:56:00.000" and "1899-12-30 03:52:00.000". Let this two be our examples. I need to add the hour values in rows together (i.e 07:56 + 03:52 = 11:48) to create another column.

DATEPART allows me to extract only the hour or minutes, which makes things more complicated.

How can I get the sum of these two values?

Upvotes: 0

Views: 78

Answers (1)

GarethD
GarethD

Reputation: 69759

The first step is to extract the time from each of your dates which you can do with CAST(Total AS TIME), Since you can't add times together, the next step is to find out the number of milliseconds (or seconds, or minutes etc depending on how accurate you want to be) since your time and midnight:

SELECT  Diff = DATEDIFF(MILLISECOND, '00:00:00', CAST(t.Total AS TIME))
FROM    (VALUES 
            (CAST('1899-12-30 07:56:00.000' AS DATETIME2)),
            (CAST('1899-12-30 03:52:00.000' AS DATETIME2))
        ) t (Total);

This gives:

Diff
---------
28560000
13920000

You can then add these values together, to get 42480000, then if you add this number of milliseconds back to midnight:

SELECT  DATEADD(MILLISECOND, 42480000, CAST('00:00:00' AS TIME))

You get 11:48. So your final query will be something like:

SELECT  Val = DATEADD(MILLISECOND, 
                        SUM(DATEDIFF(MILLISECOND, '00:00:00', CAST(t.Total AS TIME))), 
                        CAST('00:00:00' AS TIME(3)))
FROM    (VALUES 
            (CAST('1899-12-30 07:56:00.000' AS DATETIME2)),
            (CAST('1899-12-30 03:52:00.000' AS DATETIME2))
        ) t (Total);

Upvotes: 1

Related Questions