Home » SQL & PL/SQL » SQL & PL/SQL » First Day of the month  () 1 Vote
First Day of the month [message #229481] Sun, 08 April 2007 21:15 Go to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
Hi

I am having a requirement where I need to check the date and if it is less than the last day of the month, I need to get the first date else the first date of the next month

FOr Ex: I/P=30-APR-2007
Output:01-MAY-2007

I/p=29-APR-2007
O/p=01-APR-2007

I figured out the first part using LAST_dAY function, but I ma struck how to get the first day of the month.

Could you share your ideas.
Re: First Day of the month [message #229482 is a reply to message #229481] Sun, 08 April 2007 21:18 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Isn't the first of any month, day 01?
combine with
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions004.htm#sthref1008
& implement the solution

[Updated on: Sun, 08 April 2007 21:20] by Moderator

Report message to a moderator

Re: First Day of the month [message #229491 is a reply to message #229482] Sun, 08 April 2007 23:52 Go to previous messageGo to next message
fcatak
Messages: 16
Registered: March 2007
Location: turkey
Junior Member
select to_date('01' || TO_CHAR(to_date('29-APR-2007', 'dd-MON-yyyy'), 'mm') ||
               TO_CHAR(to_date('29-APR-2007', 'dd-MON-yyyy'), 'yyyy'),
               'ddmmyyyy')
  from dual
Re: First Day of the month [message #229525 is a reply to message #229491] Mon, 09 April 2007 01:37 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi,

select trunc(to_date ('09-04-2007', 'dd-MM-yyyy'),'yyyy') +
to_char((to_date(add_months('09-APR-2007',-1),'dd-mm-yyyy')),'mm') as day from dual

thanks,
srinivas
Re: First Day of the month [message #229533 is a reply to message #229481] Mon, 09 April 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2  select to_date('30-APR-2007','DD-MON-YYYY') dt from dual
  3  union all
  4  select to_date('29-APR-2007','DD-MON-YYYY') from dual
  5  )
  6  select dt, trunc(dt+1,'MONTH') from data
  7  /
DT          TRUNC(DT+1,
----------- -----------
30-APR-2007 01-MAY-2007
29-APR-2007 01-APR-2007

2 rows selected.

Regards
Michel
Re: First Day of the month [message #229547 is a reply to message #229533] Mon, 09 April 2007 02:08 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
nice

thanks
srinivas
Re: First Day of the month [message #229574 is a reply to message #229547] Mon, 09 April 2007 03:24 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select decode(to_char(sysdate,'dd-mon-yyyy'),
to_char(last_day(sysdate),'dd-mon-yyyy'),'01-'||to_char(sysdate,'mon-yyyy'),
'01-'||to_char(add_months(sysdate,-1),'mon-yyyy')) dd
from dual

[Updated on: Mon, 09 April 2007 03:25]

Report message to a moderator

Re: First Day of the month [message #229581 is a reply to message #229574] Mon, 09 April 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why my simple answer without any decode don't agree you?

Regards
Michel
Re: First Day of the month [message #229598 is a reply to message #229525] Mon, 09 April 2007 06:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pavuluri wrote on Mon, 09 April 2007 08:37
hi,

select trunc(to_date ('09-04-2007', 'dd-MM-yyyy'),'yyyy') +
to_char((to_date(add_months('09-APR-2007',-1),'dd-mm-yyyy')),'mm') as day from dual

thanks,
srinivas

SQL> select trunc(to_date ('09-04-2007', 'dd-MM-yyyy'),'yyyy') +
  2  to_char((to_date(add_months('09-APR-2007',-1),'dd-mm-yyyy')),'mm') as day from dual
  3  ;

DAY
---------
04-JAN-07

Not even close.
Do you test your scripts before you post them here or do you just consider this your brainstorm-whiteboard?

Nice one Michel! I like the way you analyzed the actual requirement prior to jumping to solutions!
Re: First Day of the month [message #229603 is a reply to message #229598] Mon, 09 April 2007 06:33 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
see once agian

select trunc(to_date ('09-04-2007', 'dd-MM-yyyy'),'yyyy') +
to_char((to_date(add_months('09-APR-2007',-1),'dd-mm-yyyy')),'mm') as day from dual

output is

1/4/2007( dd/mm/yyyy)

thanks
srinivas
Re: First Day of the month [message #229612 is a reply to message #229603] Mon, 09 April 2007 06:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Let's analyze:
select trunc(to_date ('09-04-2007', 'dd-MM-yyyy'),'yyyy') +
to_char((to_date(add_months('09-APR-2007',-1),'dd-mm-yyyy')),'mm') as day from dual
This contains:

add_months('09-APR-2007',-1) WRONG. add_months takes a date as first parameter. Not a string.

to_date(add_months('09-APR-2007',-1),'dd-mm-yyyy') WRONG. add_months returns a date. You cannot do a to_date on a date.
You take the number of months in the second argument and add that to the first of january.
In my world, that adds up to 04-jan-2007, but then again, I don't know your NLS_ settings of course.

I really don't understand the idea of your query. Reread the OPs requirements and explain how you differentiate between the last day of a month and the other days of that month.
Re: First Day of the month [message #229620 is a reply to message #229612] Mon, 09 April 2007 07:13 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i know frank u r master

i have some knowledge of oracle.

sorry ok.

thanks,
srinivas
Re: First Day of the month [message #229621 is a reply to message #229620] Mon, 09 April 2007 07:14 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
please modified my query.
thanks
srinivas
Re: First Day of the month [message #229623 is a reply to message #229620] Mon, 09 April 2007 07:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, this has nothing to do with being an expert or not or whatever. It is great to see that so many people try to help others, so please continue to do so.
The point I wanted to make is, that if you do, make sure that you either do it correctly, or add some remarks about what you try to accomplish with your query.
The way you posted your query, it is not clear how it should work.

Again, please keep it up trying to help others, just try to explain more what you do.
Re: First Day of the month [message #229625 is a reply to message #229623] Mon, 09 April 2007 07:19 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i think its correct query in my knowledge.

it is wrong please correct it.

thanks,
srinivas
Re: First Day of the month [message #229628 is a reply to message #229625] Mon, 09 April 2007 07:21 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
in those two queries which is correct
select add_months('09-APR-2007',-1) from dual

select add_months('09-04-2007',-1) from dual;

thanks,
srinivas

Re: First Day of the month [message #229629 is a reply to message #229628] Mon, 09 April 2007 07:41 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Once again, you are being completely ignorant. It seems like you purposely give wrong advice to make others as poor and sloppy as yourself. I'd love to be the person interviewing you for a job.

You don't add NUMBERS to STRINGS. Would you get that through yopur thick skull already?
Re: First Day of the month [message #229631 is a reply to message #229629] Mon, 09 April 2007 07:46 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
ok nice advise

thanks,
srinivas
Re: First Day of the month [message #229633 is a reply to message #229631] Mon, 09 April 2007 07:52 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i dont know
whats happening
iam getting same result

select add_months('09-APR-2007',-1) from dual;

select add_months(to_date('09-04-2007','DD-MM-YYYY'),-1) from dual;

thanks
srinivas
Re: First Day of the month [message #229637 is a reply to message #229633] Mon, 09 April 2007 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because your default date format is 'DD-MON-YYYY'.
Try "alter session set nls_date_format='YYYY/DD/MM'" and see what happens to your first query.

I hate default format, this should not exist.
I hate implicit conversion, this should not exist.


Regards
Michel

[emphasize my last sentences]

[Updated on: Mon, 09 April 2007 08:11]

Report message to a moderator

Re: First Day of the month [message #229638 is a reply to message #229637] Mon, 09 April 2007 08:15 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
I hate default format,

I love default format.

Thanks,
srinivas
Re: First Day of the month [message #229653 is a reply to message #229481] Mon, 09 April 2007 08:41 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
pradkuamr wrote on Sun, 08 April 2007 21:15
Hi

I am having a requirement where I need to check the date and if it is less than the last day of the month, I need to get the first date else the first date of the next month

FOr Ex: I/P=30-APR-2007
Output:01-MAY-2007

I/p=29-APR-2007
O/p=01-APR-2007

I figured out the first part using LAST_dAY function, but I ma struck how to get the first day of the month.

Could you share your ideas.


select decode(trunc(sysdate),last_day(trunc(sysdate)),trunc(sysdate+1),trunc(sysdate,'Month')
from dual;

Re: First Day of the month [message #229656 is a reply to message #229598] Mon, 09 April 2007 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks Frank for your compliment.
It seems my solution was too simple as some still post complex solutions with decode/last_day/add_months and so on.

Kind regards
Michel
Re: First Day of the month [message #229659 is a reply to message #229481] Mon, 09 April 2007 09:00 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Your right, I missed your response and using trunc(dt+1,'month') is very simple and very obvious once you see it. Nice job.
Re: First Day of the month [message #229664 is a reply to message #229659] Mon, 09 April 2007 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks.
Re: First Day of the month [message #229717 is a reply to message #229481] Mon, 09 April 2007 20:04 Go to previous message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
Thanks for your replies..I got the answer now
Previous Topic: ref cursor, collection inside a ref cursor????
Next Topic: calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL
Goto Forum:
  


Current Time: Sun Dec 04 04:17:46 CST 2016

Total time taken to generate the page: 0.09285 seconds