CODEWITHSUNDEEP
sqlsql-servert-sql
AMorrisey
AMorrisey

Reputation: 111

How to Convert 2 Varchar strings into a DateTime in SQL

I have two vchar fields that contain date and time separately as text/string.

i.e Fdate(YYYYmmdd): 20170402 Ftime(HHmmss): 103101

How would I go about concatenating them and converting into a Datetime?

Converting the date can be done, but the issue I run into is when adding the time to it.

Thank you in advance,

Upvotes: 0

Views: 84

Answers (3)

Aakash Singh
Aakash Singh

Reputation: 1060

You can use this :

Select cast (substring(Fdate,0,5)+'-'+ 
             substring(Fdate,5,2)+'-'+ 
             substring(Fdate,7,2) 
        + ' ' + 
             substring(Ftime,0,3) + ':' + 
             substring(Ftime,3,2) + ':' + 
             substring(Ftime,5,2) 
       as datetime)

Output :

yyyy-MM-dd hh:mm:ss

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

SELECT DATETIMEFROMPARTS (  substring('20170402',1,4) , substring('20170402',5,2), substring('20170402',7,2), substring('103101',1,2), substring('103101',3,2), substring('103101',5,2) ,  substring('103101',7,2) )

output

2017-04-02 10:31:01.000

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use stuff() to convert varchar time format to datetime format : 

select Fdate + cast(stuff(stuff(Ftime, 3, 0, ':'), 6, 0, ':') as datetime) as datetime

Upvotes: 0

Related Questions