Home » SQL & PL/SQL » SQL & PL/SQL » Can we find date/dates based on ..... ?
Can we find date/dates based on ..... ? [message #286911] Mon, 10 December 2007 07:24 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi All

Can we find out date or list of dates when week number(2),
day_of_the_week(TUE), month_no(10), year(2007) is given?

Please help me in this case

Thanks in Advance

Natesh
Re: Can we find date/dates based on ..... ? [message #286914 is a reply to message #286911] Mon, 10 December 2007 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes.
Have a look at TO_CHAR and EXTRACT functions.

Regards
Michel
Re: Can we find date/dates based on ..... ? [message #286917 is a reply to message #286914] Mon, 10 December 2007 07:31 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Mic

Thanks For the reply and will post the query when i get it.


Regards
Natesh
*************************************
Hi
Checked about extract function. i think it doesnt suit
my requirement. Because a date value has to be passed as
mandatory input parameter. where i need a date value from
the above given values.

Natesh

[Updated on: Mon, 10 December 2007 07:59]

Report message to a moderator

Re: Can we find date/dates based on ..... ? [message #286926 is a reply to message #286917] Mon, 10 December 2007 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Extract does not suit your purpose because it does not know the week.
I just wanted to point you and open your mind to useful date/timestamp functions.

Another function: TO_DATE.

Regards
Michel
Re: Can we find date/dates based on ..... ? [message #286927 is a reply to message #286926] Mon, 10 December 2007 08:26 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
On the job which you told Mic(opening mind Smile ). hope i get soon.
Thanks for the reply.

Natesh
Re: Can we find date/dates based on ..... ? [message #286930 is a reply to message #286911] Mon, 10 December 2007 08:55 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

to_Date function has limitation of not accepting 'W' format & 'DY' format.

When i used
select to_date('2  12 2007','dd mm yyyy') from dual;


it is giving date value. but when i use

select to_date('2 TUE 13 DEC 2007','w DY dd mon yyyy') from dual;


it is giving "FORMAT CODE CANNOT APPEAR IN DATE INPUT FORMAT"
Sad.

which is the another better approach to achieve result.

Thanks
Natesh
Re: Can we find date/dates based on ..... ? [message #286932 is a reply to message #286930] Mon, 10 December 2007 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes you have to change your view.
Generates all possible dates and then restrict to the week with to_char.

By the way, 'W' is week in the month not week in the year.

Regards
Michel
icon6.gif  Re: Can we find date/dates based on ..... ? [message #286936 is a reply to message #286932] Mon, 10 December 2007 09:16 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Mic

I got the solution from this query. Smile

SELECT date_val
  FROM (SELECT   (SELECT TRUNC (TO_DATE ('08', 'yy'), 'yy')
                    FROM DUAL)
               - 1
               + ROWNUM "DATE_VAL"
          FROM all_objects
         WHERE ROWNUM <=
                  (  (SELECT LAST_DAY (TRUNC (TO_DATE ('08', 'yy'), 'yy')
                                       + 363)
                        FROM DUAL)
                   - (SELECT TRUNC (TO_DATE ('08', 'yy'), 'yy')
                        FROM DUAL)
                   + 1
                  ))
 WHERE TO_CHAR (date_val, 'w') = 2
   AND TO_CHAR (date_val, 'mm') = 10
   AND TO_CHAR (date_val, 'DY') = 'TUE'


Can we do it more better than this? Thanks Mic.
You are in my every solution.

Regards
Natesh

[Updated on: Mon, 10 December 2007 09:17]

Report message to a moderator

Re: Can we find date/dates based on ..... ? [message #286937 is a reply to message #286936] Mon, 10 December 2007 09:19 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Mic? Who's Mic?

Since you already know what month to look in, you should not iterate over all the days in the year
Re: Can we find date/dates based on ..... ? [message #286938 is a reply to message #286937] Mon, 10 December 2007 09:22 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

No the requirement is dynamic. only year value is mandatory.
Month value is optional.

Mic in the sense Michel Cadot. Smile

Natesh

[Updated on: Mon, 10 December 2007 09:23]

Report message to a moderator

Re: Can we find date/dates based on ..... ? [message #286940 is a reply to message #286936] Mon, 10 December 2007 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I built this one that only requires to generate days for the wanted month:
SQL> !cat dt.sql
set feedback off
prompt
with 
  dates as (
    select to_date('01/&month_no/&year','DD/MM/YYYY')+level-1 dt
    from dual
    connect by level <= 31
  )
select to_char(dt,'fmDy DD Month YYYY') "Date"
from dates
where to_char(dt,'W') = &week
  and to_char(dt,'DY') = upper('&day_of_the_week')
/
prompt
set feedback on

SQL> @dt

Enter value for month_no: 12
Enter value for year: 2007
Enter value for week: 2
Enter value for day_of_the_week: mon
Date
---------------------
Mon 10 December 2007

Regards
Michel
Re: Can we find date/dates based on ..... ? [message #286941 is a reply to message #286911] Mon, 10 December 2007 10:05 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

OR one different version like


SQL> WITH PARAMSET AS  (SELECT &Y Y, &M M, &W W ,'&d' DY FROM DUAL )
  2               SELECT NEXT_DAY (ADD_MONTHS(TRUNC (TO_DATE ( Y, 'yy'), 'yy'),M-1)
  3                              + (W-1)*7 ,DY )
  4               FROM PARAMSET;
Enter value for y: 07
Enter value for m: 12
Enter value for w: 2
Enter value for d: TUE
old   1: WITH PARAMSET AS  (SELECT &Y Y, &M M, &W W ,'&d' DY FROM DUAL )
new   1: WITH PARAMSET AS  (SELECT 07 Y, 12 M, 2 W ,'TUE' DY FROM DUAL )

NEXT_DAY(
---------
11-DEC-07

SQL>


But this is a small start , byt needs some changes ..

Thumbs Up
Rajuvan.

[Updated on: Mon, 10 December 2007 10:08]

Report message to a moderator

Re: Can we find date/dates based on ..... ? [message #286942 is a reply to message #286941] Mon, 10 December 2007 10:06 Go to previous message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks Mic & Raju.. Smile

Best Regards
Natesh
Previous Topic: dbms_scheduler and program_argument_type
Next Topic: First datetime in a block
Goto Forum:
  


Current Time: Sat Feb 15 14:26:51 CST 2025