MS SQL Server: Convert Oracle Date and Custom Date Format

Product: MS SQL Server
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:

1. Period (.) is used as time seperator
2. Precision for the second is more than 3 digits precision that MS SQL Server supports
3. Empty field is stored as empty string instead of null, so CAST is converting to 1900-01-01
4. None of the cast or convert function support this format

Following is the algorithm I used to convert it:

1. Convert period (.) to colon (:) format
1. replace( ltrim(CreateTimeChar), '.', ':' )
2. Reduce the second precision to 3 decimal points which is supported for datetime2 format
1. left( CreateTimeChar, 22 ) + right( CreateTimeChar, 3)
3. Set the NULL value for empty string
1. update employee set CreateTime=null where CreateTime=''

Following is an example for an SELECT statement:

select ID, CreateTimeChar, convert (datetime2,left(replace(CreateTimeChar,'.',':'),22)+right(CreateTimeChar,3) ,6) CreateTime

from employee

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

from employee

Use replace, left, right string functions to format the date string to a supported MS SQL Server format will allow any unsupported date format.