Home » SQL & PL/SQL » SQL & PL/SQL » oracle date formatting problemo
oracle date formatting problemo [message #226817] Mon, 26 March 2007 12:18 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
Here is my situation:

In the "PERIOD" column of my oracle database it is MM/DD/YY, ie 10/07/02. I need to convert that to a different oracle date format so i can filter and sort it. What can i do??

-- Hyrum
Re: oracle date formatting problemo [message #226818 is a reply to message #226817] Mon, 26 March 2007 12:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Dates are not stored in any format, they are only displayed in format. Search for DATE FORMAT.
Re: oracle date formatting problemo [message #226819 is a reply to message #226818] Mon, 26 March 2007 12:42 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
i know that, but because dates are displayed as 1/27/99, the oracle to date will not filter and sort them or display them differently. i need to know how use that format for that purpose.
regards,

-hyrum
Re: oracle date formatting problemo [message #226824 is a reply to message #226817] Mon, 26 March 2007 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
YYYY-MM-DD will sort "properly".
Re: oracle date formatting problemo [message #226837 is a reply to message #226824] Mon, 26 March 2007 14:05 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
thanks.... but if it the actual data is showing ie "1/12/06" then how can i change that to show and change differently?
Re: oracle date formatting problemo [message #226839 is a reply to message #226837] Mon, 26 March 2007 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Back to the first answer, and search for "Datetime Format Models" and "to_char" function and "alter session" statement and... in documentation.

Regards
Michel

Re: oracle date formatting problemo [message #226841 is a reply to message #226839] Mon, 26 March 2007 14:20 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
k... i think i'm getting closer... does the oracle field type have to be the date datatype? or can the varchar work just fine?

select PERIOD from FORECAST_DATA BETWEEN TO_date('PERIOD','10/01/99') AND TO_date('PERIOD','10/09/05')

i try this statement and it throws ugly errors at me.
Re: oracle date formatting problemo [message #226844 is a reply to message #226817] Mon, 26 March 2007 14:26 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>i try this statement and it throws ugly errors at me.
I am NOT surprised.
have you tried to actually read the Fine Manual?
Re: oracle date formatting problemo [message #226847 is a reply to message #226817] Mon, 26 March 2007 14:48 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If period is a varchar2 column, (bad....bad...idea) then do the following

select PERIOD
from FORECAST_DATA
where to_date(period,'mm/dd/rr') BETWEEN TO_date('10/01/1999','mm/dd/yyyy') AND TO_date('10/09/2005','mm/dd/yyyy')

[Updated on: Mon, 26 March 2007 14:49]

Report message to a moderator

Re: oracle date formatting problemo [message #226852 is a reply to message #226847] Mon, 26 March 2007 15:06 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Thanks Bill. That works perfect!

One more question. To be able to sort the data, what's the final step to change the date format to sort properly?

All the Best,

Hyrum
Re: oracle date formatting problemo [message #226854 is a reply to message #226817] Mon, 26 March 2007 15:07 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select PERIOD
from FORECAST_DATA
where to_date(period,'mm/dd/rr') BETWEEN TO_date('10/01/1999','mm/dd/yyyy') AND TO_date('10/09/2005','mm/dd/yyyy')
order by to_date(period,'mm/dd/rr');
Re: oracle date formatting problemo [message #226856 is a reply to message #226854] Mon, 26 March 2007 15:13 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Thanks. works perfectly That concept is starting to click now. Thanks for the help Smile

Cheers -

Hyrum
Re: oracle date formatting problemo [message #226882 is a reply to message #226856] Mon, 26 March 2007 17:52 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Storing date values as strings is generally disastrous, as is omitting the century information.

A few years ago the industry spent billions fixing all those bugs, and I remember how on the TV news they described it as being due to the cost of disk space in the 1970s. What excuse do we have now?
Re: oracle date formatting problemo [message #226954 is a reply to message #226882] Tue, 27 March 2007 00:29 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ignorance, sloppyness, lazyness.
It must be the same excuse that let people rely on implicit conversions.
Previous Topic: Package
Next Topic: DECODE or CASE help
Goto Forum:
  


Current Time: Tue Dec 06 10:42:56 CST 2016

Total time taken to generate the page: 0.08887 seconds