Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert concatenated varchar date and time to datetime Expand / Collapse
Author
Message
Posted Tuesday, February 7, 2012 9:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 7, 2014 6:49 AM
Points: 79, Visits: 217
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

Post #1248279
Posted Tuesday, February 7, 2012 9:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:40 AM
Points: 327, Visits: 304
This should work

SELECT CONVERT( DATETIME, [date] + ' ' + [time] )
FROM concat_test

assuming neither of the fields are nullable
Post #1248284
Posted Tuesday, February 7, 2012 9:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 7, 2014 6:49 AM
Points: 79, Visits: 217
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.
Post #1248292
Posted Tuesday, February 7, 2012 10:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1248297
Posted Tuesday, February 7, 2012 10:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:08 AM
Points: 2,379, Visits: 7,583
See this thread for methods of ensuring that correct dates are used in your convert.


Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1248305
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse