Home » SQL & PL/SQL » SQL & PL/SQL » Convert datetime representation
Convert datetime representation [message #334446] Wed, 16 July 2008 09:45 Go to next message
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 #334449 is a reply to message #334446] Wed, 16 July 2008 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does represent the date?
What does mean 0xffff059f?

Regards
Michel
Re: Convert datetime representation [message #334452 is a reply to message #334449] Wed, 16 July 2008 10:23 Go to previous messageGo to next message
daff24
Messages: 11
Registered: June 2008
Junior Member
Michel Cadot wrote on Wed, 16 July 2008 10:07
What does represent the date?
What does mean 0xffff059f?

Regards
Michel




0xffff059f represents datetime in hexadecimal way.
Re: Convert datetime representation [message #334453 is a reply to message #334452] Wed, 16 July 2008 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the representation represents what?
Don't reply a date, but which date and why.

Regards
Michel
Re: Convert datetime representation [message #334454 is a reply to message #334446] Wed, 16 July 2008 10:29 Go to previous messageGo to next message
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 #334455 is a reply to message #334452] Wed, 16 July 2008 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
daff24 wrote on Wed, 16 July 2008 08:23

0xffff059f represents datetime in hexadecimal way.


What is the algorithm which converts the hexadecimal value to a meaningful date/time string?

What actual date/time does "0xffff059f" represent?
Re: Convert datetime representation [message #334457 is a reply to message #334446] Wed, 16 July 2008 10:42 Go to previous messageGo to next message
daff24
Messages: 11
Registered: June 2008
Junior Member
From google :

This is a 64 bit integer, the 32 bits on the left represent the number of days before or after January 1, 1900. The 32 bits on the right represent the number of 1/300th of a second since midnight.

Hexadecimal values and the corresponding date

0x0000000000000000 Jan 1 1900 12:00AM
0x00000000018B81FF Jan 1 1900 11:59PM
0xFFFF2E4600000000 Jan 1 1753 12:00AM
0x002D247F00000000 Dec 31 9999 12:00AM

http://www.dba-sql-server.com/sql_server_tips/t_super_sql_382_smalldatetime.htm

[Updated on: Wed, 16 July 2008 10:42]

Report message to a moderator

Re: Convert datetime representation [message #334462 is a reply to message #334457] Wed, 16 July 2008 11:03 Go to previous messageGo to next message
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)
, or if you're going to do a lot of these conversions, wrap it in a function like this:
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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: need help to create index to optimize query
Next Topic: How many OUT REF CURSOR arguments can be defined in a procedure
Goto Forum:
  


Current Time: Fri Mar 29 04:57:17 CDT 2024