Converting SQL Date format to Oracle Date format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting SQL Date format to Oracle Date format

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-28 : 11:45:42

Hi,

I have a column date in my database which I should send it to Oracle database. The Date format in Oracle is number. I don’t know how should I convert the date to that format?
Example :
SQL Format Oracle Format
02/16/05 105046

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 12:45:29

Could you describe how to calculate the Oracle format? Is it the number of seconds since a certain date, which I believe is sometimes referred to as a Java date? If so, I've got a function to do the conversion which can be found on my blog.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-28 : 12:49:35

Dear Tara ,
My qouestion is "how to calculate the Oracle format?"

Thank you for your reply

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 12:53:26

I haven't a clue. Most of us here have very little experience with Oracle. I'd suggest posting your question on an Oracle forum and then when you get the answer, come back to us with how to calculate it and then we can point you to the correct code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-28 : 12:59:19

I know How to Convert that number to date format.

DECLARE @mysterydate int
SET @mysterydate = 105046

SELECT DATEADD(day, @mysterydate % 1000, DATEADD(year, @mysterydate / 1000, 0))

But now I have date formate and I need that number.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 13:23:48

This produces the correct output but I'm not sure if the calculation is right since we haven't been given an explanation:

DECLARE @dt datetime
SET @dt = '2005-02-16'

SELECT CONVERT(int, RIGHT('000' + CONVERT(varchar(3), DATEDIFF(year, 0, @dt)), 3) + RIGHT('000' + CONVERT(varchar(3), DATEDIFF(day, DATEADD(year, DATEDIFF(year, 0, @dt), 0), @dt)), 3))

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-28 : 13:35:45

Thank you so much Tara.
That is what I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources