CODEWITHSUNDEEP
sql-server-2008datetime
mdivk
mdivk

Reputation: 3727

What is the easiest way to convert a custom time format to timestamp in SQL Server

I have a data set with the time field in the format below:

29052016:09:01

Can anyone share with your easiest way to convert it into datetime in SQL Server?

Thank you very much.

Upvotes: 0

Views: 46

Answers (1)

Squirrel
Squirrel

Reputation: 24783

Since you did not provide us any information on what is that string of numbers represent, i just have to make a wild guess that it represent Year 2016 Month 05 Day 29 Hour 09 Minute 01. And based on that, you can use stuff() to format it as dd/mm/yyyy hh:mm then use convert() with style 103 to convert to datetime

declare @str varchar(20) = '29052016:09:01'

select  convert(datetime ,stuff(stuff(stuff(@str, 3, 0, '/'), 6, 0, '/'), 11, 1, ' '), 103)

If I totally make the wrong assumption, please ignore this answer.

Upvotes: 2

Related Questions