CODEWITHSUNDEEP
sql-serveroracle-databasedatedatetime
Stupid Dream
Stupid Dream

Reputation: 140

Convert Oracle Datetime format query to MS SQL Server Format

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.

Upvotes: 3

Views: 1714

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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

Upvotes: 2

Related Questions