Home » SQL & PL/SQL » SQL & PL/SQL » transform field
transform field [message #189199] Wed, 23 August 2006 10:48 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello

How can I calculate the end of the month date from a field in number format (e.g. 200608, the field means YYYYMM)?

Example: I have '200608' and want to get '31.8.2006'

Thanks
Stefan
Re: transform field [message #189201 is a reply to message #189199] Wed, 23 August 2006 10:54 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select last_day(to_date('01'||to_char(date_num),'ddmmyyyy'))
from my_table;

[Updated on: Wed, 23 August 2006 10:55]

Report message to a moderator

Re: transform field [message #189210 is a reply to message #189199] Wed, 23 August 2006 11:25 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

i think this will help u.

select
to_char(last_day(to_date(dat_num,'yyyymm')),'dd.mm.yyyy')
from my_table;
Re: transform field [message #189211 is a reply to message #189199] Wed, 23 August 2006 11:29 Go to previous message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
select to_char(last_day(to_date(dat_num,'yyyymm')),'dd.mm.yyyy') from my_table
Previous Topic: eliminate leading zeros on text field
Next Topic: how to call the below function in select query
Goto Forum:
  


Current Time: Tue Dec 06 14:29:34 CST 2016

Total time taken to generate the page: 0.09545 seconds