Help to convert Char to Datetime Format

Question

Help to convert Char to Datetime Format

Mac1986

SSCertifiable

Points: 6216
More actions
January 26, 2017 at 10:57 am

#318186

Hello All,
I'm stuck at a point where I'm unable to convert the below char to Datetime format. can someone help me get through this. then i'll all be golden in my ETL.
Char to be converted: '2017/01/26:08:13:55'
Expected format is DateTime.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Mac1986 - Thursday, January 26, 2017 10:57 AM
Hello All,
I'm stuck at a point where I'm unable to convert the below char to Datetime format. can someone help me get through this. then i'll all be golden in my ETL.
Char to be converted: '2017/01/26:08:13:55'
Expected format is DateTime.

If this is how all the dates are represented in character form, the first thing you need to do is replace that ":" between the date and time with a space. After doing that the string should convert. To be sure I would also replace the "/" with a "-".

Mac1986 SSCertifiable Points: 6216 More actions · Answer 2

Mac1986 - Thursday, January 26, 2017 10:57 AM
Hello All,
I'm stuck at a point where I'm unable to convert the below char to Datetime format. can someone help me get through this. then i'll all be golden in my ETL.
Char to be converted: '2017/01/26:08:13:55'
Expected format is DateTime.

Thanks for the tip. I used below conversion.

SELECT CAST(SUBSTRING('2017/01/26:08:13:55',1,10) + ' ' + SUBSTRING('2017/01/26:08:13:55',12,19) AS datetime)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Lynn Pettis - Thursday, January 26, 2017 11:06 AM
Mac1986 - Thursday, January 26, 2017 10:57 AM
Hello All,
I'm stuck at a point where I'm unable to convert the below char to Datetime format. can someone help me get through this. then i'll all be golden in my ETL.
Char to be converted: '2017/01/26:08:13:55'
Expected format is DateTime.
If this is how all the dates are represented in character form, the first thing you need to do is replace that ":" between the date and time with a space. After doing that the string should convert. To be sure I would also replace the "/" with a "-".

There are several formats which are independent of any language settings. Read more about those in here: https://technet.microsoft.com/en-us/library/ms180878%28v=sql.105%29.aspx#UsingDateandTimeFormats
For dates only:

YYYYMMDD

For time only:

hh:mi:ss.mss

For date and time:

YYYY-MM-DDThh:mi:ss.mss
YYYY-MM-DD hh:mi:ss.mss (this is not affected by dateformat only for datetime2 and datetimeoffset)
YYYYMMDD hh:mi:ss.mss

That being said, I prefer to remove the "-" to keep some consistency.
To achieve this formats, REPLACE and STUFF will be of great help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2