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.

Can you change the format mask of the TO_CHAR function? If so than you can use the ISO 8601 'yyyy-mm-ddThh:mm:ss.mmm' format mask that will allow you the direct cast of the character representation of a Oracle date to the DATETIME data type of Microsoft SQL Server. This is true for Microsoft SQL Server 2005 and upwards. — Andrei Odegov, May 27 '17 at 14:35
Alter the format mask you use in Oracle to produce a string which will convert conveniently. By the way to achieve the required level of precision you need datetime2, and if all this fails to help consider reading the answers here https://stackoverflow.com/questions/29200147/how-do-i-convert-an-oracle-timestamp-data-type-to-sql-server-datetime2-data-type — Paul Maxwell, May 28 '17 at 01:54

Shnugo · Answer 1 · 2017-05-27T19:01:49.213

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

Paul Maxwell · Answer 2 · 2017-05-28T03:12:16.427

Although I don't really understand the need to use a string format that does not suit conversion, but you could divide the string into parts then build it up by adding the parts to each other. The foundation part if the first 8 characters converted to datetime2 using format style 5.

select
      t
    , convert(varchar, converted ,121) converted
from (
        select '29-03-17 03:58:34.312000000 PM' as t
     ) t
cross apply (
    select 
        convert(datetime2,substring(t,1,8),5) dt2
      , case when right(t,2) = 'PM' then convert(smallint,substring(t,10,2)) + 12
             else convert(smallint,substring(t,10,2))
        end                                 hh
      , convert(smallint,substring(t,13,2)) mi
      , convert(smallint,substring(t,16,2)) ss
      , convert(int,substring(t,19,9))      ns
      ) ca
cross apply (
    select
        dateadd(hh,hh,dateadd(mi,mi,dateadd(ss,ss,dateadd(ns,ns,dt2))))
        as converted
      ) ca2
;

Note I am able to use the column aliases of the first cross apply (dt1, hh, mi, ss, ns) in the second cross apply to form the converted datetime2 value.

+--------------------------------+-----------------------------+
|               t                |          converted          |
+--------------------------------+-----------------------------+
| 29-03-17 03:58:34.312000000 PM | 2017-03-29 15:58:34.3120000 |
+--------------------------------+-----------------------------+

see: http://rextester.com/DZJ42703

Convert Oracle Datetime format query to MS SQL Server Format

2 Answers2

EDIT: The above is culture related!

Another approach