Home » SQL & PL/SQL » SQL & PL/SQL » datatype INTEGER to datatype DATE
datatype INTEGER to datatype DATE [message #252289] Wed, 18 July 2007 08:50 Go to next message
amlunda
Messages: 5
Registered: July 2007
Location: Norway
Junior Member
Hi, I got a table with a colum defined as INTEGER holding a date, eg 40750 that is the date 27.07.2011 (dd.mm.yyyy)
How do I write the select for converting the INTEGER number to a DATE format that I can understand?
Re: datatype INTEGER to datatype DATE [message #252294 is a reply to message #252289] Wed, 18 July 2007 09:01 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
select decode(col1,40750,to_date('27.07.2011','dd.mm.yyyy'))
  from dual;
Re: datatype INTEGER to datatype DATE [message #252295 is a reply to message #252294] Wed, 18 July 2007 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing
Re: datatype INTEGER to datatype DATE [message #252297 is a reply to message #252295] Wed, 18 July 2007 09:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If I assume correctly that '01-Jan-1900' is '1' then :

SELECT To_Date('31.12.1899','dd.mm.yyyy') + 40750 FROM dual;


might work.
Re: datatype INTEGER to datatype DATE [message #252382 is a reply to message #252297] Wed, 18 July 2007 13:27 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Thomas. Heh, I was assuming that the 40750 was the number of days from some certain day, but the dyslexia in me typed 47050, and since subtracting that from the 2011 date looked like some random date, I went with my answer since I could see no correlation.
If I drank coffee, I would have used Maarten's line of needing one, but I guess I need to type more carefully.
Re: datatype INTEGER to datatype DATE [message #252536 is a reply to message #252289] Thu, 19 July 2007 02:28 Go to previous messageGo to next message
amlunda
Messages: 5
Registered: July 2007
Location: Norway
Junior Member
Hi, I think i did explain my problem to bad Embarassed
So I try again: I want to search a table for records where a certain column holds the DATE as an Integer value (eg 40750).
If the DATE was stored as a Datatype = DATE the select would be as this:
"select date,x,y,z from table where to_date(date,'dd.mm.yy')= to_date('18.07.07','dd.mm.yy')"
But what is correct when the DATE is stored as an Integer value? I know only that 40750 is equal with the date 26.07.11, but I have no clue about witch value other dates do have.So what I need help with is to convert the date from an understandable format to INTEGER format.
Re: datatype INTEGER to datatype DATE [message #252541 is a reply to message #252536] Thu, 19 July 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you didn't define how to translate your integer to a date.
For instance, why 40750 is 27.07.2011?

Regards
Michel
Re: datatype INTEGER to datatype DATE [message #252552 is a reply to message #252289] Thu, 19 July 2007 02:54 Go to previous messageGo to next message
amlunda
Messages: 5
Registered: July 2007
Location: Norway
Junior Member
In the application the date is displayed as an understandable date, so there I see the date 26.7.2007.
In the database this is stored as 40750.
I did dype 40750 in Excel and convert the column to date and then it is 26.7.2007 automatically, so I assume the number is possible to convert also in Oracle.
Re: datatype INTEGER to datatype DATE [message #252558 is a reply to message #252541] Thu, 19 July 2007 03:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

... date is displayed as an understandable date, so there I see the date 26.7.2007. In the database this is stored as 40750.



Quote:

... 40750 that is the date 27.07.2011 ....



Which of these two is right?

[Updated on: Thu, 19 July 2007 03:01]

Report message to a moderator

Re: datatype INTEGER to datatype DATE [message #252564 is a reply to message #252289] Thu, 19 July 2007 03:14 Go to previous messageGo to next message
amlunda
Messages: 5
Registered: July 2007
Location: Norway
Junior Member
sorry...26.7.2011 is correct
Re: datatype INTEGER to datatype DATE [message #252566 is a reply to message #252564] Thu, 19 July 2007 03:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then use just the other way around form what I posted earlier :

SELECT To_Date('27.07.2011','dd.mm.yyyy') - To_Date('31.12.1899','dd.mm.yyyy') FROM dual


to get the integer for 27.07.2011 or any other date you supply.
Re: datatype INTEGER to datatype DATE [message #252569 is a reply to message #252289] Thu, 19 July 2007 03:26 Go to previous message
amlunda
Messages: 5
Registered: July 2007
Location: Norway
Junior Member
Thanks Cool
I did need it with a spoon, but now I understand
Previous Topic: need logic with decode
Next Topic: prior clause confusion
Goto Forum:
  


Current Time: Wed Dec 07 22:22:05 CST 2016

Total time taken to generate the page: 0.06419 seconds