Home » SQL & PL/SQL » SQL & PL/SQL » Conversion to Date Format
Conversion to Date Format [message #36323] Fri, 16 November 2001 06:31 Go to next message
Amit Karnik
Messages: 3
Registered: November 2001
Junior Member
I have 2 columns namely Month and Year where in both r of Number type.Month comes in the form of 1,2,3 and so on and year is in form 'RRRR'.Now I want a single column where in i want the type of field to be DATE and it should be in the form DD-MMM-RRRR where DD=01 default ,MM = Month eg (apr for April which i get it as 4 ) and year as RRRR eg 2000

----------------------------------------------------------------------
Re: Conversion to Date Format [message #36326 is a reply to message #36323] Fri, 16 November 2001 07:04 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
-- populate date column with existing month and year column values
 
update t
  set newdatecol = to_date(month || '/01/' || year, 'mm/dd/yyyy');
 
-- retrieve results in desired format
 
select to_char(newdatecol, 'DD-Mon-RRRR')
  from t


----------------------------------------------------------------------
Re: Conversion to Date Format [message #36330 is a reply to message #36323] Fri, 16 November 2001 08:54 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
That's exactly what the code I provided does. If your new date column is a date datatype, it will store a date value, but it is up to you to format it on the select the way you want to see it.

That's why I gave the select example, which if you would test it, would retrieve your date as:

01-Apr-2000

Try it - it works.

----------------------------------------------------------------------
Re: Conversion to Date Format [message #36334 is a reply to message #36323] Fri, 16 November 2001 10:08 Go to previous messageGo to next message
tony montana
Messages: 1
Registered: November 2001
Junior Member
I see what you have done in terms of the default to one being /01/, but the problem is if you are actually given a day as well, it won't work. The only way it works is with Month and year given only.

----------------------------------------------------------------------
Re: Conversion to Date Format [message #36336 is a reply to message #36330] Fri, 16 November 2001 12:52 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Why the concern about day? He says he only has year and month columns.

This is not supposed to be some all-purpose conversion routine - it's based on the requirements provided ("I have year and month. How do I get these into a date column and retrieve the value in a certain format?").

----------------------------------------------------------------------
Re: Conversion to Date Format [message #36338 is a reply to message #36323] Sat, 17 November 2001 01:19 Go to previous message
Amit Karnik
Messages: 3
Registered: November 2001
Junior Member
Hi,I wont be able to test the same til Monday,bcos i wil have to check it in my office as we have the dummy data there.
So will let u know the same on Monday

Is there any way by wich i can do the same without changing the Column of the existing table?
Like writing any function for the same or something of that sort

Thanks

----------------------------------------------------------------------
Previous Topic: Evaluating Logical Expression
Next Topic: Trigger problem @ Execution
Goto Forum:
  


Current Time: Thu Mar 28 23:42:23 CDT 2024