Convert Oracle Datetime format query to MS SQL Server Format

Question

I have a Oracle query

SELECT to_timestamp('29-03-17 03:58:34.312000000 PM','DD-MM-RR HH12:MI:SS.FF AM') 
FROM DUAL

I want to convert to SQL Server where I need to retain the Oracle date string i.e '29-03-17 03:58:34.312000000 PM':

SELECT 
    CONVERT(DATETIME, REPLACE(REPLACE('29-03-2017 03:58:34.312000000 PM','-', '/'),'000000 ', ''), 131)

I tried the above query, as 131 format closely matches '29-03-17 03:58:34.312000000 PM' format 'dd/mm/yyyy hh:mi:ss:mmmAM' but only difference is with the year.

In Oracle year is 17 and SQL Server the year is 2017. I need to prefix 20 to the year to make it 2017. This query converts into Hijri datetime. I need it in Gregorian datetime format.

This is the documentation.

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

I need to convert the date which is in string in Oracle format to SQL Server equivalent. Is there any way where the format like 'dd/mm/yyyy hh:mi:ss:mmmAM' can be mentioned instead of mentioning the date format code like 131, 101, 102 in the convert function.

Answer 1

You might try it like this:

DECLARE @oracleDT VARCHAR(100)='29-03-17 03:58:34.312000000 PM';
SELECT CAST('<x>' + @oracleDT + '</x>' AS XML).value(N'(/x/text())[1]','datetime');

It seems, that XML is implicitly able to do this correctly...

EDIT: The above is culture related!

It worked on my (german) system, but if you set the correct dateformat you can force this (be aware of side effects for the current job!)

Try this and then remove the -- to try alternative date formats. Or try with GERMAN:

SET LANGUAGE ENGLISH;
SET DATEFORMAT mdy;
--SET DATEFORMAT ymd;
--SET DATEFORMAT dmy;
DECLARE @oracleDT VARCHAR(100)='01-02-03 03:58:34.312000000 PM';
SELECT CAST('<x>' + @oracleDT + '</x>' AS XML).value(N'(/x/text())[1]','datetime');

Another approach

You might split the string in all parts and build a convertible format like this:

DECLARE @oracleDT VARCHAR(100)='29-03-17 03:58:34.312000000 PM';
WITH AllParts(Casted) AS
(
    SELECT CAST('<x>' + REPLACE(REPLACE(REPLACE(REPLACE(@oracleDT,'.','-'),' ','-'),':','-'),'-','</x><x>') + '</x>' AS XML)
)  
SELECT CONVERT
      (DATETIME,
               DATENAME(MONTH,'2000'+Casted.value(N'x[2]/text()[1]','nvarchar(max)')+'01') + ' '
             + Casted.value(N'x[1]/text()[1]','nvarchar(max)') + ' '
     + N'20' + Casted.value(N'x[3]/text()[1]','nvarchar(max)') + ' '
             + Casted.value(N'x[4]/text()[1]','nvarchar(max)') + ':'
             + Casted.value(N'x[5]/text()[1]','nvarchar(max)') + ':'
             + Casted.value(N'x[6]/text()[1]','nvarchar(max)') + ':'
             + LEFT(Casted.value(N'x[7]/text()[1]','nvarchar(max)'),3)
             + Casted.value(N'x[8]/text()[1]','nvarchar(max)'),109)
FROM AllParts