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.
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.