CODEWITHSUNDEEP
sqlsql-server
user2328935
user2328935

Reputation: 25

Convert date and int time as datetime

I have a column MyDate as date and TimeStart as int. how can I convert the below image as date time in SQL like 2019-11-01 00:06

enter image description here

Upvotes: 0

Views: 121

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You haven't told us which DBMS you are using. In standard SQL you'd add n minutes thus:

<some_date> + <number_of_minutes> * interval '1' minute

(At least that's how I read the standard syntax, where the interval literal is defined as

<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval qualifier>

In your case:

select mydate + timestart * interval '1' minute from mytable;

Some DBMS may allow your column in place of the "interval string":

select mydate + interval timestart minute from mytable;

UPDATE: For SQL Server use

select dateadd(minute, timestart, mydate) from mytable;

Another update: I just saw that timestart 145 means 1 hour and 45 minutes. This is about the worst way to store datetime information possible. You want another calculation then:

select dateadd(minute, timestart / 100 * 60 + timestart % 100, mydate) from mytable;

But what you really should do is fix your data model and make your date and obfuscated time one datetime column. (Well, maybe this is exactly what you are in the process of doing just now :-)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271121

If you want a time, then add minutes to the value:

select convert(time, dateadd(minute, (timecol / 100) + timecol % 60, 0))

Of course, you can just add this to mydate column:

select dateadd(minute, (timecol / 100) + timecol % 60, mydate)

Upvotes: 1

Related Questions