Re: How to calculate Last and First Day of Month?
Thanks to Jared and others for a quick assistance. Regards, Viktor --- [EMAIL PROTECTED] wrote: Well, yes the first day of the month is always 1. Representing it as the first day of the current month requires you to use the SYSDATE function. Returning the first day of the month can be done in several ways, some more reasonable than others. there's the new to SQL basic programmer DBA wannabe method: select substr(to_char(sysdate,'MM/DD/'), 1,2) || '/01/' || substr(to_char(sysdate,'MM/DD/'), 7) from dual; There's the slightly better: select to_date(to_char(sysdate,'MM') || '01','MMDD') from dual; The obtuse date math method ( my favorite ): select last_day(add_months(sysdate,-1))+1 from dual; ( there are several variations on this method ) A little RTFM will reveal a much cleaner method: select trunc(sysdate,'mm') from dual; So, the answer is always 1, but there is more than one way to get there. Jared SA, OCP, and Part Time Perl Evangelist Charlie Mengler [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/23/02 12:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: How to calculate Last and First Day of Month? I would have thought that the first day of the month is ALWAYS the 1st, as in 1 (ONE)! Please explain why it needs to be calculated or could be a value other than ONE. [EMAIL PROTECTED] 01/23/02 10:35AM Hey guys, I know this is propablby easy, but I'm a bit overwhelmed here this week. Can you please tell me how to get first and last days of the month given SYSDATE? thanks a lot Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to calculate Last and First Day of Month?
I would have thought that the first day of the month is ALWAYS the 1st, as in 1 (ONE)! Please explain why it needs to be calculated or could be a value other than ONE. [EMAIL PROTECTED] 01/23/02 10:35AM Hey guys, I know this is propablby easy, but I'm a bit overwhelmed here this week. Can you please tell me how to get first and last days of the month given SYSDATE? thanks a lot Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to calculate Last and First Day of Month?
Charlie Mengler wrote: I would have thought that the first day of the month is ALWAYS the 1st, as in 1 (ONE)! Please explain why it needs to be calculated or could be a value other than ONE. [EMAIL PROTECTED] 01/23/02 10:35AM Hey guys, I know this is propablby easy, but I'm a bit overwhelmed here this week. Can you please tell me how to get first and last days of the month given SYSDATE? thanks a lot Regards Another case of 'On what day does Xmas fall this year? - on December 25th'. I think that using the TRUNC() function with the suitable parameter must help you truncate SYSDATE to the first day of the month - perhaps TRUNC(SYSDATE, 'MM') or similar (too tired to RTFM). Then TO_CHAR with the suitable format should return whatever you want. For the last day, I presume that identifying the first day of the NEXT month and substracting 1 must simplify the 'is this 30/31/28/29' question. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to calculate Last and First Day of Month?
It is the 1st. I've already got quick help on that... thanks --- Charlie Mengler [EMAIL PROTECTED] wrote: I would have thought that the first day of the month is ALWAYS the 1st, as in 1 (ONE)! Please explain why it needs to be calculated or could be a value other than ONE. [EMAIL PROTECTED] 01/23/02 10:35AM Hey guys, I know this is propablby easy, but I'm a bit overwhelmed here this week. Can you please tell me how to get first and last days of the month given SYSDATE? thanks a lot Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to calculate Last and First Day of Month?
select last_day(add_months(sysdate,-1)) from dual; -Original Message- Sent: Wednesday, January 23, 2002 2:38 PM To: Multiple recipients of list ORACLE-L It is the 1st. I've already got quick help on that... thanks --- Charlie Mengler [EMAIL PROTECTED] wrote: I would have thought that the first day of the month is ALWAYS the 1st, as in 1 (ONE)! Please explain why it needs to be calculated or could be a value other than ONE. [EMAIL PROTECTED] 01/23/02 10:35AM Hey guys, I know this is propablby easy, but I'm a bit overwhelmed here this week. Can you please tell me how to get first and last days of the month given SYSDATE? thanks a lot Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Weaver, Walt INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to calculate Last and First Day of Month?
First day calculations; select last_day(add_months(sysdate,-1))+1 form dual; for current month 1st day. select last_day(add_months(sysdate,-1)) from dual ; for end of previous month. ROR [EMAIL PROTECTED] 01/23/02 10:35AM Hey guys, I know this is propablby easy, but I'm a bit overwhelmed here this week. Can you please tell me how to get first and last days of the month given SYSDATE? thanks a lot Regards __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to calculate Last and First Day of Month?
Well, yes the first day of the month is always 1. Representing it as the first day of the current month requires you to use the SYSDATE function. Returning the first day of the month can be done in several ways, some more reasonable than others. there's the new to SQL basic programmer DBA wannabe method: select substr(to_char(sysdate,'MM/DD/'), 1,2) || '/01/' || substr(to_char(sysdate,'MM/DD/'), 7) from dual; There's the slightly better: select to_date(to_char(sysdate,'MM') || '01','MMDD') from dual; The obtuse date math method ( my favorite ): select last_day(add_months(sysdate,-1))+1 from dual; ( there are several variations on this method ) A little RTFM will reveal a much cleaner method: select trunc(sysdate,'mm') from dual; So, the answer is always 1, but there is more than one way to get there. Jared SA, OCP, and Part Time Perl Evangelist Charlie Mengler [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/23/02 12:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: How to calculate Last and First Day of Month? I would have thought that the first day of the month is ALWAYS the 1st, as in 1 (ONE)! Please explain why it needs to be calculated or could be a value other than ONE. [EMAIL PROTECTED] 01/23/02 10:35AM Hey guys, I know this is propablby easy, but I'm a bit overwhelmed here this week. Can you please tell me how to get first and last days of the month given SYSDATE? thanks a lot Regards -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to calculate Last and First Day of Month?
the first is always the first and the last may be calculated by using last_day function. -Original Message- Sent: Wednesday, January 23, 2002 4:32 PM To: Multiple recipients of list ORACLE-L Charlie Mengler wrote: I would have thought that the first day of the month is ALWAYS the 1st, as in 1 (ONE)! Please explain why it needs to be calculated or could be a value other than ONE. [EMAIL PROTECTED] 01/23/02 10:35AM Hey guys, I know this is propablby easy, but I'm a bit overwhelmed here this week. Can you please tell me how to get first and last days of the month given SYSDATE? thanks a lot Regards Another case of 'On what day does Xmas fall this year? - on December 25th'. I think that using the TRUNC() function with the suitable parameter must help you truncate SYSDATE to the first day of the month - perhaps TRUNC(SYSDATE, 'MM') or similar (too tired to RTFM). Then TO_CHAR with the suitable format should return whatever you want. For the last day, I presume that identifying the first day of the NEXT month and substracting 1 must simplify the 'is this 30/31/28/29' question. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
