CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rakhmi
Rakhmi

Reputation: 33

Convert string to time using SQL

I have two columns, Date and Time. the columns as following below :

Date Time
16/03/11 1812

I want to combine the two columns using SQL query into a date format so that it becomes a format like this :

yyyy-mm-dd hh mm ss -> 2011-03-16 18:12:00

What is the correct query to get the format?

Upvotes: 0

Views: 2809

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

You may use CONVERT here along with a bit of string manipulation:

SELECT Date, Time,
       CONVERT(datetime, Date + ' ' + LEFT(RIGHT('00' + Time, 4), 2) + ':' +
                                      RIGHT(RIGHT('00' + Time, 4), 2), 3) AS dt
FROM yourTable;

screen capture from demo link below

Demo

I first left pad the time with zeroes to a length of 4 characters. This is to ensure that we always can accurately take the left 2 characters as the hour, and the right two as the minutes.

Needless to say, you can see from the complexity above that storing dates as text can get messy. It would be much better to just use a bona fide SQL Server date column for this.

Upvotes: 1

Related Questions