Add a Time column and a Date column together?

Question

Add a Time column and a Date column together?

rkelly58

SSCrazy

Points: 2151
More actions
September 30, 2020 at 1:52 pm

#3791944

Hi,
Please can someone help with. I am trying to create a new column containing a combination of two existing columns.
Column 1 is datetime and column 2 is smallint
Example
2020-11-20 00:00:00.000 900
2020-11-21 00:00:00.000 1700
I want to create a new column which is a comb of Column 1 and 2
End result:
2020-11-20 09:00:00.000
2020-11-21 09:00:00.000
I tried myself with : SELECT
CONVERT(datetime, CONVERT(varchar(10), Column2 / 100)+ ':' + CONVERT(varchar(10), Column2% 100))
This gets a new column as a datetime, but doesn't contain the proper date.
Any help appreciated.
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

I would personally convert your int representation of a Time (which i really recommend changing, there's a time datatype specifically for storing times) to a time. Then get the difference in minutes, as it appears your value is accurate to a minute, from midnight to that time and add that your your datetime column:

WITH YourTable AS(
    SELECT *
    FROM (VALUES(CONVERT(datetime,'2020-11-20T00:00:00.000'), 900),
                (CONVERT(datetime,'2020-11-21T00:00:00.000'), 1700))V(D,T))
SELECT DATEADD(MINUTE,DATEDIFF(MINUTE,'00:00:00',V.T),YT.D) AS NewDateTime
FROM YourTable YT
     CROSS APPLY (VALUES(TRY_CONVERT(time,STUFF(RIGHT(CONCAT('0000',YT.T),4),3,0,':'))))V(T);

The TRY_CONVERT is there, as I don't doubt you have at least one bad value in your Time column (like 960 or 2402). For such values, NULL will be returned.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Here is another possibility, which separately adds the hours and minutes to the starting date:

DECLARE @d DATETIME = '20201120';
DECLARE @i SMALLINT = 725;

SELECT @d
,      @i
,      DATEADD(MINUTE,c.mins,DATEADD(HOUR, c.hrs, @d))
FROM
(SELECT hrs = @i / 100, mins = @i - (@i/100) * 100) c;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rkelly58 SSCrazy Points: 2151 More actions · Answer 3

Hi Thom,

thanks for your swift reply.

I cannot change the source data, as it's from a fixed source and comes through as is with Time column as a Smallint.

Checking the times, they are all int and accurate to a minute, no need to worry about seconds.

Cannot you work your SQL magic using Datetime as Date and Time as smallint?

rkelly58 SSCrazy Points: 2151 More actions · Answer 4

Hi Phil,

Any idea how i change your script to use my actual values in my table columns? - Sorry my sql is fairly poor

Thom A SSC Guru Points: 99130 More actions · Answer 5

rkelly58 wrote:

Cannot you work your SQL magic using Datetime as Date and Time as smallint?

That's exactly what my solution does treat the data as... D is defined as a datetime, and T as an int. You can see this in the sample data:

(VALUES(CONVERT(datetime,'2020-11-20T00:00:00.000'), 900))V(D,T)

It's in the solution I change the data types. I just recommend that you actually fix the data type in the source in my post as well.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

rkelly58 wrote:

Hi Phil,
Any idea how i change your script to use my actual values in my table columns? - Sorry my sql is fairly poor

Here's the same example using a temp table:

DROP TABLE IF EXISTS #dates;

CREATE TABLE #dates
(
    SomeDate DATETIME
,   SomeInt  SMALLINT
);

INSERT #dates
(
    SomeDate
,   SomeInt
)
VALUES
('20201120', 725);

SELECT d.SomeDate
,      d.SomeInt
,      DATEADD(MINUTE, c.mins, DATEADD(HOUR, c.hrs, @d))
FROM #dates d
    CROSS APPLY
(
    SELECT hrs = d.SomeInt / 100
    ,      mins = d.SomeInt - (d.SomeInt / 100) * 100
) c;

This reply was modified 4 years, 1 month ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Sorry... Dropped the post... I'll be back...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rkelly58 SSCrazy Points: 2151 More actions · Answer 8

Hi Thom,

Feel daft asking, but instead of the values you manually inserted here:

FROM (VALUES(CONVERT(datetime,'2020-11-20T00:00:00.000'), 900),

(CONVERT(datetime,'2020-11-21T00:00:00.000'), 1700))V(D,T))

How do i get it to point at my actual table and get the values from the two columns?

Sorry

Thom A SSC Guru Points: 99130 More actions · Answer 9

rkelly58 wrote:

Hi Thom,
Feel daft asking, but instead of the values you manually inserted here:
FROM (VALUES(CONVERT(datetime,'2020-11-20T00:00:00.000'), 900),
(CONVERT(datetime,'2020-11-21T00:00:00.000'), 1700))V(D,T))
How do i get it to point at my actual table and get the values from the two columns?
Sorry

That's also in the answer:

SELECT DATEADD(MINUTE,DATEDIFF(MINUTE,'00:00:00',V.T),YT.D) AS NewDateTime
FROM YourTable YT
     CROSS APPLY (VALUES(TRY_CONVERT(time,STUFF(RIGHT(CONCAT('0000',YT.T),4),3,0,':'))))V(T);

I called it YourTable because you would actually use your table there instead (and obviously change the column references to your columns, D being the date column, and Tthe time).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Sorry about the previous post... I had an extra "60" in it instead of a 100.

Anyway, using the integer math suggestion and the test table setup that Phil posted along with a tweak to the integer math, this serious simplification should suffice to sufficiently suffoncify the stated problem and anything else should be superfluous . 😀

 SELECT  SomeDate
        ,SomeInt
        ,SomeDT = DATEADD(mi,SomeInt/100*60+SomeInt%100,SomeDate)
   FROM #Dates
;

This reply was modified 4 years, 1 month ago by Jeff Moden. Reason: Made fix to change "ss" to "mi" in the code

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 11

DATE and TIME are available data types that can be combined into the newer DATETIME2(n) data type. Just use the CAST() function and simple math. The use of an integer for the temporal data type is fundamentally wrong; would you use a color for a time?

Please post DDL and follow ANSI/ISO standards when asking for help.

rkelly58 SSCrazy Points: 2151 More actions · Answer 12

Hi Jeff,

Here is the result of your code:

Date StartTime SomeDT

2020-11-21 00:00:00.000 930 2020-11-21 00:09:30.000

The SomeDT seems wrong, shoudn't it read: 2020-11-21 09:30:00.000

is the formula wrong?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

rkelly58 wrote:

Hi Jeff,
Here is the result of your code:
Date StartTime SomeDT
2020-11-21 00:00:00.000 930 2020-11-21 00:09:30.000
The SomeDT seems wrong, shoudn't it read: 2020-11-21 09:30:00.000
is the formula wrong?

Yep... thank you for the catch. I was using seconds instead of minutes. I can only blame a coffee deprivation. 😀 The fix is simple. We just need to change "ss" to "mi", like the following... I'll also fix the original post.

 SELECT  SomeDate
        ,SomeInt
        ,SomeDT = DATEADD(mi,SomeInt/100*60+SomeInt%100,SomeDate)
   FROM #Dates
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

jcelko212 32090 wrote:

DATE and TIME are available data types that can be combined into the newer DATETIME2(n) data type. Just use the CAST() function and simple math. The use of an integer for the temporal data type is fundamentally wrong; would you use a color for a time?

A lot of that is true although the DATETIME2(n) datatype is relatively crippled (not to mention NOT meeting ISO standards) compared to DATETIME() and so I avoid DATETIME2() when ever I can unless it's thrust upon me.

Also, just to be sure, the OP stated that they weren't the ones that are responsible for the poor datatyping that you're correctly talking about. A 3rd party is providing the data and they, apparently, can't be compelled to make the change that needs to happen and that's why the OP asked for a bit of help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)