Version: v3.0 - 2018
MS SQL Server has very limited formatting conversion for date field, especially those hosted in Europe.
This post will show how to convert following format to illustrate how to convert unsupported format that produced by Oracle.
Date format: DD-MON-YY HH.MI.SS.SSS AM
Date value: 03-MAY-19 11.04.37.752000000 PM
Issue with above format are below:
- Period (.) is used as time seperator
- Precision for the second is more than 3 digits precision that MS SQL Server supports
- Empty field is stored as empty string instead of null, so CAST is converting to 1900-01-01
- None of the cast or convert function support this format
Following is the algorithm I used to convert it:
- Convert period (.) to colon (:) format
- replace( ltrim(CreateTimeChar), '.', ':' )
- Reduce the second precision to 3 decimal points which is supported for datetime2 format
- left( CreateTimeChar, 22 ) + right( CreateTimeChar, 3)
- Set the NULL value for empty string
- update employee set CreateTime=null where CreateTime=''
select ID, CreateTimeChar, convert (datetime2,left(replace(CreateTimeChar,'.',':'),22)+right(CreateTimeChar,3) ,6) CreateTime
Following is an example for an INSERT statement:
insert into EmployeeConvert (ID, CreateTime)
select ID, convert (datetime2,left(replace(CreateTimeChar,'.',':'),22)+right(CreateTimeChar,3) ,6) CreateTime
Use replace, left, right string functions to format the date string to a supported MS SQL Server format will allow any unsupported date format.