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!
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);