CODEWITHSUNDEEP
sql-server
Rahul Sukumar
Rahul Sukumar

Reputation: 23

Combine two string columns to datetime

I have a table in SQL Server 2016 with two columns, date and time.

Both are varchar with date stored as '2020-10-01' and time stored as '2103'. 2103 is 9:03pm.

I need to query this table such that I get one column in datetime form. Any ideas?

Upvotes: 1

Views: 1184

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81960

Perhaps a simple concat() and stuff()

Example

Declare @YourTable Table ([DateCol] varchar(50),[TimeCol] varchar(50))  
Insert Into @YourTable Values 
 ('2020-10-01',2103)
 
Select *
      ,AsDateTime = try_convert(datetime,concat(DateCol,' ',stuff(TimeCol,3,0,':')) )
 from @YourTable

Returns

DateCol     TimeCol   AsDateTime
2020-10-01  2103      2020-10-01 21:03:00.000

Upvotes: 1

seanb
seanb

Reputation: 6685

Probably the safest and easiest way to convert it, is to create a varchar string with a specific format (see https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 for formats).

Then you can CONVERT it to a date safely - using CONVERT (rather than CAST) allows you to specify the format.

Here I'm going for format 126 - yyyy-mm-ddThh:mi:ss.mmm

Note that for the string conversions here I'm assuming 2020-10-01 here refers to 1st October rather than 10 January, and that times have a leading 0 if it's before 10:00am.

The example below shows the string you want to get to with a CONVERT.

SELECT CONVERT(datetime, '2020-10-01T21:03:00', 126)

So, to run the convert, here's an approach.

SELECT CONVERT(datetime, yourDateField + 'T' + LEFT(yourTimeField,2) + ':' + RIGHT(yourTimeField,2) + ':00', 126)

Here's a DB<>fiddle with example.

Note that you need to be careful when converting dates - as I found out from @AaronBertrand recently, the format '2020-10-01' is not a universal format - see the comments For each quarter between two dates, add rows quarter by quarter in SQL SERVER

Upvotes: 1

Related Questions