|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Date Time problem in sql server [message #656136 is a reply to message #656124] |
Fri, 23 September 2016 16:49   |
 |
Barbara Boehmer
Messages: 9082
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 #656170 is a reply to message #656138] |
Mon, 26 September 2016 09:16 |
Bill B
Messages: 1971
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 |
 |
sss111ind
Messages: 634
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
