CODEWITHSUNDEEP
sqldatetimetimeconcatenationvarchar
Dan Neville
Dan Neville

Reputation: 13

T-SQL 2008 Convert Date and time string to datetime

I have two columns in my table, one to capture time and one to capture date. Unfortunately, both are varchar(). I need to take the two fields, concatenate them together, and then convert them to datetime.

I am trying to accomplish that with this:

select CONVERT(datetime,(select txt_returned_date+' '+CONVERT(varchar(20),CONVERT(TIME,txt_time_returned))),126) 
from table_name

I am getting this error message:

Conversion failed when converting date and/or time from character string.

The date is being captured as "20130308" as a string. Time is being captures as "4:27 PM" as a string

What I am doing here is converting the string of the time to TIME, then back to varchar. Then I am concatenating them together. This works by itself, but once I introduce the CONVERT(datetime) to the whole query, it is giving me the error.

Any help to try to accomplish this is helpful. Thanks!

Upvotes: 1

Views: 20880

Answers (3)

steoleary
steoleary

Reputation: 9278

Are you using SQL 2012? If so you may be able to use the datetimedromparts function to achieve this. If not for this specific example, it's always good to know for the future :)

http://technet.microsoft.com/en-gb/library/hh213233.aspx

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

You can use this option

DECLARE @date date = '20010101',
        @time time = '01:01:01'

SELECT CAST(@date AS datetime) + @time

Result:2001-01-01 01:01:01.000

Demo on SQLFiddle

Upvotes: 2

sqlfool
sqlfool

Reputation: 248

You can concatenate the DATE and TIME values together once they have been converted to a DATETIME. Here's a sample to play with that shows concatenating a DATE column and a TIME column that have been stored as VARCHAR: 

-- Set up some variables to test with
DECLARE @myTime TIME = GETDATE()
    , @myDate DATE = GETDATE()
    , @myTimeTxt VARCHAR(16)
    , @myDateTxt VARCHAR(10);

-- Initialize your variables
SELECT @myTimeTxt = @myTime
    , @myDateTxt = @myDate;

-- Display your separated values
SELECT @myDateTxt, @myTimeTxt;

-- Display your concatenated value
SELECT CAST(@myDateTxt AS DATETIME) + CAST(CAST(@myTimeTxt AS TIME) AS DATETIME);

Upvotes: 4

Related Questions