CODEWITHSUNDEEP
sqlsql-server
user2096512
user2096512

Reputation: 469

SQL: Consolidate Date and time column into 1 DateTime column

I've made a bit of a mess of my data import and have ended up with the following columns:

Date (Type = INT) (DataFormat = 20131231)
Time (Type = datetime) (DataFormat = 1899-12-30 13:30:00.000)

I would like to end up with 1 column for Date/Time as datetime type, keeping the existing dates and times, is there a way to convert my existing columns?

Upvotes: 1

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Here is one method:

declare @Date int = 20131231
declare @DateTime datetime = '1899-12-30 13:30:00.000'

select dateadd(ms, 
               datediff(ms, cast(@DateTime as date), @DateTime),
               cast(cast(@Date as varchar(8)) as datetime)
              )

Upvotes: 4

Related Questions