CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Lynchie
Lynchie

Reputation: 1149

Combine Separate Date and Time Columns SQL

I have a database I'm working with where the morons that designed the front end software deemed it necessary to create a separate Date and Time field however leaving the Date columns a DATETIME column and the Time as a VARCHAR column.

Essentially my Date and Time looks like this:

Date | Time

2007-10-06 00:00:00.000 | 1135

Instead of just combining the 2 under 1 column as a proper datetime.

I am trying to combine the 2 together so I can acheive exactly this, a proper DATETIME field that combines both sets of information into i.e.

2007-10-06 11:35:00.000

This bit I am struggling with.

I thought I could get away with 

DECLARE @date DATETIME
DECLARE @time VARCHAR

SET @date = '2007-10-06 00:00:00.000'
SET @time = '1125'

SELECT TOP 1
    @date + ( SUBSTRING(@time , 1 , 2) + ':' + SUBSTRING(@time , 2 , 2)
              + ':00' )

Unfortunately not, now I think I am overthinking the problem and just bumping into wall after wall.

Can anyone assist in this matter and point out to me where I am going wrong :/

Upvotes: 1

Views: 184

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Here is how i would write it

DECLARE @date DATETIME = '2007-10-06T00:00:00'
DECLARE @time VARCHAR(4) = '1125'

SELECT @date + stuff(@time, 3,0, ':')

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Define a size for varchar variable @time. The default is 1.

You also need to change the second substring to 3,2.

DECLARE @date DATETIME
DECLARE @time VARCHAR(4)

SET @date = '2007-10-06 00:00:00.000'
SET @time = '1125'

SELECT TOP 1
    @date + ( SUBSTRING(@time , 1 , 2) + ':' + SUBSTRING(@time , 3 , 2)
              + ':00' )

Upvotes: 3

Related Questions