| Conversion to Date Format [message #36323] |
Fri, 16 November 2001 06:31  |
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  |
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 |
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 |
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 |
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 |
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
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
