Home » SQL & PL/SQL » SQL & PL/SQL » printing first day of every month (oracle 10g)
printing first day of every month [message #667468] Mon, 01 January 2018 04:25 Go to next message
kumarravik
Messages: 32
Registered: January 2016
Location: delhi
Member
Hi Folks,


I want to write a PL/SQL query that should display first day of every month.
And another query that will display the date of second Friday of the every month.

could you please suggest?

I am new to PL/SQL and trying to understand how date time functions work.

"select to_char(sysdate,'day') from dual" is returning the day of date but i here date is supplied already.

how i will print the day when only year is known to me?

Report message to a moderator

Re: printing first day of every month [message #667469 is a reply to message #667468] Mon, 01 January 2018 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

To get the first day of a specific date month use TRUNC(...,'MONTH').
To get the first day of every month then use a row generator, start with Jan 1st and use ADD_MONTHS to go to the next month dates. There are examples there.
For your other problem, have a look at NEXT_DAY function.

All datetime functions are listed there.

Try to do it and then come back with your tries.

Report message to a moderator

Re: printing first day of every month [message #667470 is a reply to message #667468] Mon, 01 January 2018 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
"select to_char(sysdate,'day') from dual" is returning the day of date but i here date is supplied already.

It returns the current day at 0:00; SYSDATE also contains the time part but the Oracle default display format is to display the date without the time part. Have a look at TO_CHAR function and Datetime format models section to get this time part.

Report message to a moderator

Re: printing first day of every month [message #667474 is a reply to message #667470] Mon, 01 January 2018 08:28 Go to previous messageGo to next message
kumarravik
Messages: 32
Registered: January 2016
Location: delhi
Member
Thanks Michel for quick and efficient solution.

Now trying to get the second Tuesday of the month. here's what i have written.

SELECT hire_DATE+MOD((17-TO_CHAR(hire_date,'D')),7)+7 FROM employees where employee_id=207

it works perfectly. do i need to add +14 when i need to get third Tuesday ?

Report message to a moderator

Re: printing first day of every month [message #667475 is a reply to message #667474] Mon, 01 January 2018 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Sorry, I don't understand your formula. If you want the second Tuesday of the month of a specific date, it does not work:
SQL> with dt as (select SYSDATE+9 dt from dual)
  2  SELECT to_char(dt, 'DY DD-MON-YYYY','nls_date_language=american') dt,
  3         to_char(dt+MOD((17-TO_CHAR(dt,'D')),7)+7,'DY DD-MON-YYYY','nls_date_language=american') "2nd Tuesday?"
  4  FROM dt;
DT              2nd Tuesday?
--------------- ---------------
WED 10-JAN-2018 TUE 23-JAN-2018
But the second Tuesday of January is 9.

Remind: Rule #12 of OraFAQ Forum Guide:

Quote:
If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

So post the solution you found for the first problem.

Report message to a moderator

Re: printing first day of every month [message #667478 is a reply to message #667475] Mon, 01 January 2018 13:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Mon, 01 January 2018 09:52

Sorry, I don't understand your formula.
But the second Tuesday of January is 9.
It looks like OP uses terms loosely. Formula he/she posted is second Tuesday from HIRE_DATE. And it is NLS-dependent (which you already fixed) and is more complex than needed:

with t as (
           select  add_months(trunc(sysdate,'mm'),level - 1) fisrt_of_the_month
             from  dual
             connect by level <= 24
          )
select  to_char(fisrt_of_the_month,'FMDay, Month DD YYYY') fisrt_day_of_the_month,
        to_char(trunc(fisrt_of_the_month + 5,'iw') + 8,'FMDay, Month DD YYYY') second_tuesday_of_the_month_sy,
        TO_CHAR(fisrt_of_the_month + MOD((17 - TO_CHAR(fisrt_of_the_month,'D','nls_date_language=american')),7) + 7,'FMDay, Month DD YYYY') second_tuesday_of_the_month_op
  from  t
/

FISRT_DAY_OF_THE_MONTH         SECOND_TUESDAY_OF_THE_MONTH_SY SECOND_TUESDAY_OF_THE_MONTH_OP
------------------------------ ------------------------------ ------------------------------
Monday, January 1 2018         Tuesday, January 9 2018        Tuesday, January 9 2018
Thursday, February 1 2018      Tuesday, February 13 2018      Tuesday, February 13 2018
Thursday, March 1 2018         Tuesday, March 13 2018         Tuesday, March 13 2018
Sunday, April 1 2018           Tuesday, April 10 2018         Tuesday, April 10 2018
Tuesday, May 1 2018            Tuesday, May 8 2018            Tuesday, May 8 2018
Friday, June 1 2018            Tuesday, June 12 2018          Tuesday, June 12 2018
Sunday, July 1 2018            Tuesday, July 10 2018          Tuesday, July 10 2018
Wednesday, August 1 2018       Tuesday, August 14 2018        Tuesday, August 14 2018
Saturday, September 1 2018     Tuesday, September 11 2018     Tuesday, September 11 2018
Monday, October 1 2018         Tuesday, October 9 2018        Tuesday, October 9 2018
Thursday, November 1 2018      Tuesday, November 13 2018      Tuesday, November 13 2018
Saturday, December 1 2018      Tuesday, December 11 2018      Tuesday, December 11 2018
Tuesday, January 1 2019        Tuesday, January 8 2019        Tuesday, January 8 2019
Friday, February 1 2019        Tuesday, February 12 2019      Tuesday, February 12 2019
Friday, March 1 2019           Tuesday, March 12 2019         Tuesday, March 12 2019
Monday, April 1 2019           Tuesday, April 9 2019          Tuesday, April 9 2019
Wednesday, May 1 2019          Tuesday, May 14 2019           Tuesday, May 14 2019
Saturday, June 1 2019          Tuesday, June 11 2019          Tuesday, June 11 2019
Monday, July 1 2019            Tuesday, July 9 2019           Tuesday, July 9 2019
Thursday, August 1 2019        Tuesday, August 13 2019        Tuesday, August 13 2019
Sunday, September 1 2019       Tuesday, September 10 2019     Tuesday, September 10 2019
Tuesday, October 1 2019        Tuesday, October 8 2019        Tuesday, October 8 2019
Friday, November 1 2019        Tuesday, November 12 2019      Tuesday, November 12 2019
Sunday, December 1 2019        Tuesday, December 10 2019      Tuesday, December 10 2019

24 rows selected.

SQL>

SY.

Report message to a moderator

Re: printing first day of every month [message #667555 is a reply to message #667475] Fri, 05 January 2018 04:37 Go to previous messageGo to next message
kumarravik
Messages: 32
Registered: January 2016
Location: delhi
Member
Apologies for delay response.

Here's what I have done to get the first date of every month.

SELECT
TRUNC
(ADD_MONTHS(SYSDATE,1*LEVEL),'MONTH')
FROM DUAL
CONNECT BY LEVEL <=12

Now to get the day for these dates, added the to_char function.

SELECT TO_CHAR (
TRUNC (ADD_MONTHS(SYSDATE,1*LEVEL),'MONTH'),'day' )
FROM DUAL
CONNECT BY LEVEL <=12


Now to get second Tuesday, I have used the next_day function.

select
next_day(
(trunc(sysdate,'mm')-1), 'TUE' ) + 7
from dual

you can add +14 to get 3rd Tuesday and +21 to get 4th.

Also 'TUE can be replaced with others day as well i.e. 'SAT', 'SUN' .


Sorry about the format. I don't really know how to format these texts here. In preview, the SQL code doesn't appear the way I typed.

[Updated on: Fri, 05 January 2018 04:43]

Report message to a moderator

Re: printing first day of every month [message #667556 is a reply to message #667555] Fri, 05 January 2018 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
At the top of this forum is a sticky post titled: How to use code tags and make your code easier to read
read it

Report message to a moderator

Re: printing first day of every month [message #667559 is a reply to message #667555] Fri, 05 January 2018 07:16 Go to previous message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Your solution is NLS dependent and will not work for non-english speaking database clients:

SQL> alter session set nls_date_language=french;

Session altered.

SQL> select 
  2  next_day( 
  3  (trunc(sysdate,'mm')-1), 'TUE' ) + 7 
  4  from dual
  5  /
(trunc(sysdate,'mm')-1), 'TUE' ) + 7
                         *
ERROR at line 3:
ORA-01846: not a valid day of the week


SQL>

And unfortunately NEXT_DAY doesn't have NLSPARAM to provide date language like, for example, TO_DATE does. If you want to use NEXT_DAY and make it NLS independent you can use a trick based on January 4, 0001 being Tuesday (or pick any other date that is Tuesday):

SQL> alter session set nls_date_language=american;

Session altered.

SQL> select 
  2  next_day( 
  3  (trunc(sysdate,'mm')-1),to_char(date '1-1-4','day' /* Tuesday in session date language */)) + 7
  4  from dual
  5  /

NEXT_DAY(
---------
09-JAN-18

SQL> alter session set nls_date_language=french;

Session altered.

SQL> select 
  2  next_day( 
  3  (trunc(sysdate,'mm')-1),to_char(date '1-1-4','day' /* Tuesday in session date language */)) + 7
  4  from dual
  5  /

NEXT_DAY((T
-----------
09-JANV.-18

SQL>

Or use ISO format IW like in my example.

SY.

Report message to a moderator

Previous Topic: Procedure with multiple search filter
Next Topic: SQL query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Apr 27 02:59:37 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.