February 7, 2012 at 9:46 am
Hello, I have to columns in a table of which both are a varchar datatype. I need to concatenate them and convert them to a datetime. Here is an example
create table concat_test
(date varchar(8),
time varchar(10)
)
insert into concat_test
values('20101208', '1:30 PM')
insert into concat_test
values('20101209', '2:30 PM')
insert into concat_test
values('20101210', '3:30 PM')
I would like to concatenate to two columns together like mm-dd-yyyy hh:mi:ssAM.
Example:
12-10-2010 03:30:00PM
February 7, 2012 at 9:50 am
This should work
SELECT CONVERT( DATETIME, [date] + ' ' + [time] )
FROM concat_test
assuming neither of the fields are nullable
February 7, 2012 at 9:57 am
This works fine on my test data but when I try it on actual data, I get the following message:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
February 7, 2012 at 10:01 am
That's because you have data somewhere in there that can't be converted to a datetime.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2012 at 10:07 am
See this thread for methods of ensuring that correct dates are used in your convert.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply