question

Snow avatar image
Snow asked

Date time format yyyy-mm-ddThh:mm:ss.nnn

Hi There, I'm a newbie to SQL and currently trying to learn. I imported a .csv file into a SQL table using SQL import wizard. There is a date time field that I have loaded as a text - nvarchar(255). The date time format I believe is yyyy-mm-ddThh:mm:ss.nnn. An example of a value in this field is 2017-11-19T14:46:07:125. Is there a way that I can break the above date time string into yyyy-mm-dd and hh:mm:ss.nnn in to two different columns? In the time filed the time should show in 12 hour clock instead of 24 hour clock. Applicate if anyone could help.
datetime
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you confirm the format of the data? You said it is yyyy-mm-ddThh:mm:ss.nnn, but then the example had a colon between seconds and milliseconds 2017-11-19T14:46:07:125
0 Likes 0 ·
Hi Kev, thank you so much for the reply. Sorry for my typo error. It should be 2017-11-19T14:46:07.125, no colon before millisecond. Basically what I'm trying to do here is loading these DateTime values from .csv to a SQL table. Then split Date and Time portions but Time should show as 12 hour clock format. Currently I have loaded to SQL as text (nvarchar). Hi Magnus, Thank you so much for the reply too. I will give a try to use those functions to see whether I can get the result out. Once again thanks to both of you for trying to help me!!
0 Likes 0 ·
In that case, the answer from @Magnus should get you going in the right direction!
0 Likes 0 ·

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You can use TRY_CONVERT(date, the_string_value) and TRY_CONVERT(time, the_string_value) respectively. I suggest TRY_CONVERT given that you now store the values in a varchar(255) column, thus the data is not validated by any constraints or datatypes. How you choose to present the data is another issue, you could use eg the FORMAT function to format the time column. I'm not a big fan of formatting stuff in database code, I think that's an application issue. The DB code, in my opinion, should just make sure to return correct values of correct data types.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What I actually did was created a select query with a sub query in it. In the sub query I used the TRY_CONVERT(Date,FieldName)as A and TRY_CONVERT(Time,FieldName) as B to split the string into Date and Time. Then in the outermost select statement I used Convert (varchar,B,100) to convert the time to 12 hour clock format, for an example this resulted the 24 hour time format '14:46:07.125' to 12 hour time format '2.46PM'. However it ignored seconds and milliseconds which is OK for the moment but good to have them included too. Tomorrow at work I will try using Format function as u suggested. Thank you so much for the help, This was my very first SQL work and would love to share the joy after getting the result.
2 Likes 2 ·
I'm so exited, the function TRY_CONVERT() works and I manage to break the Date and Time part from the string. and then I gonna use the function CONVERT() to get the 12 hour clock format. Thank you so much Magnus!!
0 Likes 0 ·
I guess you mean you will use the FORMAT-function to format the time-portion to AM/PM-format? I wasn't able to use the FORMAT-function properly on a time-datatype, so I instead used a datetime-datatype. Like this: declare @s varchar(100)='2017-11-19T14:46:07.125'; select format(try_convert(datetime,@s),'hh:mm tt'), try_convert(date,@s);
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.