Home » SQL & PL/SQL » SQL & PL/SQL » Date Time problem in sql server (Oracle 10g)
Date Time problem in sql server [message #656124] Fri, 23 September 2016 10:26 Go to next message
sss111ind
Messages: 579
Registered: April 2012
Location: India
Senior Member

Hi All,

While migrating data from sql server to oracle ,the date time is getting generated as hexadecimal format. So it need to convert while inserting into oracle. Please help us to convert sql server (hexadecimal ) to oracle date.

Please find the insert script where the date is generated as hexadecimal needs to convert as date in oracle while inserting.

INSERT [dbo].[TBL_temp] ([ID],  [CREATEDDATE]) VALUES (9,  CAST(0x0000A58300A92EAE AS DateTime));

Thanks you in advance.
Re: Date Time problem in sql server [message #656125 is a reply to message #656124] Fri, 23 September 2016 11:23 Go to previous messageGo to next message
Littlefoot
Messages: 21365
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm probably too stupid.

Out of all possible formats, you chose a hexadecimal number to migrate datetime values. How come?

I can't read hexa numbers, so I converted that value to a decimal number. It is 181982070386350. Now, what would that be? 18.1982.07. 03.86.350) - 18th of July 1982; what would the rest be? 03 hours 86 minutes etc.? I don't think so.

However, if you could explain what 0x0000A58300A92EAE or 181982070386350 stand for, someone might be able to assist. I'm afraid I can't; at least, not now.
Re: Date Time problem in sql server [message #656126 is a reply to message #656125] Fri, 23 September 2016 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
Is it the number of seconds from 1 January 1970?
Re: Date Time problem in sql server [message #656128 is a reply to message #656126] Fri, 23 September 2016 11:47 Go to previous messageGo to next message
Littlefoot
Messages: 21365
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That would be too much, I'm afraid. 181982070386350 seconds is 5.77 million years.
Re: Date Time problem in sql server [message #656130 is a reply to message #656128] Fri, 23 September 2016 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or maybe microseconds or the like since then.
But the best way is to search in SQL Server documentation or ask in a SQL Server forum what it is, isn't sss111ind?
And then you come back with your question, but then is there still a question?

Re: Date Time problem in sql server [message #656131 is a reply to message #656130] Fri, 23 September 2016 13:18 Go to previous messageGo to next message
sss111ind
Messages: 579
Registered: April 2012
Location: India
Senior Member

Thank you all for your reply. The script was generated from sql server . Any date time column present in the table being generated as hexadecimal format only. We tried in sql developer scratch editor but unable to convert. And tried migrate option to migrate but connection problem occurs. So the best possible to migrate the data from sq server to oracle is through db link only. Please let me know any other option is available to migrate the data.

Regards
Nathan
Re: Date Time problem in sql server [message #656132 is a reply to message #656131] Fri, 23 September 2016 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you read the SQL Server documentation or ask SQL Server forum what the data is?

Re: Date Time problem in sql server [message #656133 is a reply to message #656132] Fri, 23 September 2016 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
what is the actual correct Date/time for 0x0000A58300A92EAE ????????????????????
Re: Date Time problem in sql server [message #656134 is a reply to message #656133] Fri, 23 September 2016 13:39 Go to previous messageGo to next message
Littlefoot
Messages: 21365
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you mentioned SQL Developer: did you read Migrating from Microsoft SQL Server to Oracle?
Quote:

Oracle SQL Developer is a free and fully supported product that provides tools and utilities to migrate from Microsoft SQL Server to Oracle.
Re: Date Time problem in sql server [message #656136 is a reply to message #656124] Fri, 23 September 2016 16:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8805
Registered: November 2002
Location: California, USA
Senior Member
According to my research, the 8 characters from the 3rd through 10th hexadecimal characters, when converted to a number, represent the number of days since January 1st of 1900. The next 8 characters from the 11th through 18th hexidecimal characters, when converted to a number, represent the time as the number of ticks, where there are 300 ticks per second. Please see the following demonstration for inserting your SQL Server hexadecimal datetime data into an Oracle date column.

-- test data:
SCOTT@orcl_12.1.0.2.0> create table sql_server_data
  2    (hex_date_time  varchar2(18))
  3  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert into sql_server_data (hex_date_time) values ('0x0000A58300A92EAE')
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> select * from sql_server_data
  2  /

HEX_DATE_TIME
------------------
0x0000A58300A92EAE

1 row selected.

-- oracle table:
SCOTT@orcl_12.1.0.2.0> create table oracle_table
  2    (oracle_date  date)
  3  /

Table created.

-- insert that converts sql server hexadecimal datetime to oracle date with time:
SCOTT@orcl_12.1.0.2.0> insert into oracle_table (oracle_date)
  2  select to_date ('19000101', 'yyyymmdd') +
  3  	      to_number (substr (hex_date_time, 3, 8), 'xxxxxxxx') +
  4  	      to_number (substr (hex_date_time, 11), 'xxxxxxxx') / 300 / 60 / 60 / 24
  5  from   sql_server_data
  6  /

1 row created.

-- results:
SCOTT@orcl_12.1.0.2.0> select * from oracle_table
  2  /

ORACLE_DATE
------------------------
mon 04-jan-2016 10:15:58

1 row selected.

Re: Date Time problem in sql server [message #656137 is a reply to message #656136] Fri, 23 September 2016 21:55 Go to previous messageGo to next message
sss111ind
Messages: 579
Registered: April 2012
Location: India
Senior Member

Thank you so much for your effort. I will check this and come back soon as the weekend starts I have no access to the office system. I am sure it will work.

Regards
Nathan
Re: Date Time problem in sql server [message #656138 is a reply to message #656137] Sat, 24 September 2016 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why is this not YOU who made this research?

Re: Date Time problem in sql server [message #656170 is a reply to message #656138] Mon, 26 September 2016 09:16 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Personally I have found that the easiest way to convert into an oracle date is to convert the date/time on sql server into a string and then use to_date with the correct format mask to insert into oracle.

Re: Date Time problem in sql server [message #656171 is a reply to message #656170] Mon, 26 September 2016 10:07 Go to previous message
sss111ind
Messages: 579
Registered: April 2012
Location: India
Senior Member

Thank you so much,

We have searched many forums and tried it also but unable to get the proper answer and finally we got here.It is working nicely.


select '2014-01-08 15:46:18.673' sqlserver_date,'0x0000A2AD0103E982' sqlserver_hexaval,
      to_date ('19000101', 'yyyymmdd') +
    	      to_number (substr ('0x0000A2AD0103E982', 3, 8), 'xxxxxxxx') +
    	      to_number (substr ('0x0000A2AD0103E982', 11), 'xxxxxxxx') / 300 / 60 / 60 / 24 oracle_date
    from   dual;


output
----------
SQLSERVER_DATE           SQLSERVER_HEXAVAL       ORACLE_DATE
2014-01-08 15:46:18.673	0x0000A2AD0103E982	08-01-2014 15:46:19

Regards
Nathan
Previous Topic: Nested Query- Result Achieved. Performance improvement ?
Next Topic: Column Data Modification
Goto Forum:
  


Current Time: Fri Feb 23 04:58:19 CST 2018

Total time taken to generate the page: 0.01396 seconds