OraFAQ Forum: SQL & PL/SQL » Want to get first day of last month

Home » SQL & PL/SQL » SQL & PL/SQL » Want to get first day of last month (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Want to get first day of last month [message #609501]

Fri, 07 March 2014 14:05

cplusplus1
Messages: 58
Registered: October 2012
Location: usa

Member

I am using the following condition it is working fine on toad sql dev, but the trunc is not recongnized in the etl tool, is there another way getting 1st day of last month without using trunc.
I pass the date as this: '01-Jan-2014'

where calendar_day = TRUNC(TRUNC(TO_DATE('01-Jan-2014', 'DD-MON-YYYY') , 'Month')-1 , 'Month');

Thank you very much for the helpful info.

Report message to a moderator

Re: Want to get first day of last month [message #609502 is a reply to message #609501]

Fri, 07 March 2014 14:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use TO_CHAR

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Fri, 07 March 2014 14:11]

Report message to a moderator

Re: Want to get first day of last month [message #609503 is a reply to message #609501]

Fri, 07 March 2014 14:33

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select to_date('01-'||to_char(add_months(TO_DATE('01-Jan-2014', 'DD-MON-YYYY'),-1),'MON-YYYY'),'DD-MON-YYYY') from dual;
TO_DATE('01
-----------
01-DEC-2013

[Updated on: Fri, 07 March 2014 14:33]

Report message to a moderator

Re: Want to get first day of last month [message #609505 is a reply to message #609501]

Fri, 07 March 2014 14:39

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

orclz>
orclz> select last_day(add_months(TO_DATE('01-Jan-2014', 'DD-MON-YYYY'),-2))+1 from dual;

LAST_DAY(ADD_MONTHS
-------------------
2013-12-01 00:00:00

orclz>

Report message to a moderator

Previous Topic:	create new record from LAST record with SQL
Next Topic:	user parameters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:02:11 CDT 2024