| Convert datetime representation [message #334446] |
Wed, 16 July 2008 09:45  |
daff24
Messages: 11
Registered: June 2008
|
Junior Member |
|
|
Hi!
I have an INSERT-script which is generated from sql server 2005 using MS Database Publishing Wizard.
INSERT INTO table(id, date) VALUES (1, CAST(0xffff059f AS DATETIME))
Is there any way I can convert this datetime-representaion so it could be inserted into Oracle database datetime column? Is there any function for this?
Thank you!
[Updated on: Wed, 16 July 2008 09:56] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Convert datetime representation [message #334454 is a reply to message #334446] |
Wed, 16 July 2008 10:29  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm guessing that 0xffff059f is a Hex number ffff059f with a leading 0x to indicate this, probably a piece of raw data and that when converted to base 10, it can be used as a data time in some Julian format.
Given the size of the number when it's converted (4294903199), it's probably seconds after some date.
Sysdate - 4294903199/(24*60*60) is 10th June 1972, which is the right ballpark for a startdate in an old IT system.
If I'm right, then the dates are convertible if you can find the start date to calculate from.
However, I've made a big sack full of assumptions, only some of which I'd bet more than a pint of beer on being right.
|
|
|
|
|
|
|
|
| Re: Convert datetime representation [message #334462 is a reply to message #334457] |
Wed, 16 July 2008 11:03 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The description you've posted is for a DATETIME. The data you've posted is more similar to a SMALLDATETIME.
Assuming that to be the case. you can use this:to_date('01-01-1900','dd-mm-yyyy')
+ to_number(substr(ltrim(replace('0xffff059f','0x',' ')),1,4),'XXXX')
+ to_number(substr(ltrim(replace('0xffff059f','0x',' ')),5,4),'XXXX')/(24*60*60)CREATE OR REPLACE FUNCTION convert_date (p_hex in varchar2) return date as
v_string varchar2(10);
BEGIN
v_string := ltrim(replace('0xffff059f','0x',' '));
return to_date('01-01-1900','dd-mm-yyyy')
+ to_number(substr(v_string,1,4),'XXXX')
+ to_number(substr(v_string,5,4),'XXXX')/(24*60*60);
END;
/ |
|
|
|
| Re: Convert datetime representation [message #334467 is a reply to message #334457] |
Wed, 16 July 2008 11:25 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| daff24 wrote on Wed, 16 July 2008 17:42 | ...
|
And why don't you post all these informations in the first post?
Do you think if I post an hexadecimal value of Oracle timestamp you should be able to give me the conversion to SQL Server one?
Regards
Michel
[Updated on: Wed, 16 July 2008 11:25] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
