Home » SQL & PL/SQL » SQL & PL/SQL » date function (oracle 10.2)
icon9.gif  date function [message #355145] Wed, 22 October 2008 21:03 Go to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
I have a table called p_date.The table structure and data is attached .(data for 2months are attached)

I want to query the week_id for a particular month .
I can do so by
select disticnt week_id from p_date where date_id between '01-OCT-2008' and '31-Oct-2008'. But there will be some weeks which is common to both months.
In this case the week will go to that month , on which its friday will fall.
example last week of October .The friday falls on october 31.So the week will be counted in October calculation.

Can some one help.
  • Attachment: d.csv
    (Size: 2.54KB, Downloaded 113 times)
Re: date function [message #355147 is a reply to message #355145] Wed, 22 October 2008 21:37 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@konark,

I didn't understand your requirement properly. Please post your desired output and the output you are getting.

Before that please read OraFAQ Forum Guide on How to Format Post.

Regards,
Jo
Re: date function [message #355148 is a reply to message #355147] Wed, 22 October 2008 21:44 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
My desired output

QUERY : SELECT ALL WEEKS FOR A PARTICULAR MONTH

refer the attached table and its data

Desired output for October
--------------------------
MONTH_ID WEEK_ID
2008410 200839
2008410 200840
2008410 200841
2008410 200842
2008410 200843


Desired output for NOVEMBER
--------------------------
2008411 200844
2008411 200845
2008411 200846
2008411 200847
Re: date function [message #355163 is a reply to message #355148] Wed, 22 October 2008 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain your output.
For instance why "2008410"?

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 22 October 2008 23:25]

Report message to a moderator

Re: date function [message #355194 is a reply to message #355148] Thu, 23 October 2008 01:25 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Konark,

Can you please post the desired result of September based on your table. I am bit confused with your desired output of October. Desired result of September might clear up some things for me.


Sorry.... Got it... Please ignore this post...

Regards,
Jo

[Updated on: Thu, 23 October 2008 01:28]

Report message to a moderator

Re: date function [message #355211 is a reply to message #355145] Thu, 23 October 2008 02:43 Go to previous messageGo to next message
sundha
Messages: 6
Registered: November 2007
Junior Member
Hi Konark,


The following query might be useful for you......

SELECT week_id,month_id
FROM
(SELECT week_id,month_id, DECODE(trim(TO_CHAR(date_id,'day')),'friday',1,0) status
FROM pk_date WHERE date_id BETWEEN '01-NOV-2008' AND '30-NOV-2008')
GROUP BY week_id,month_id
HAVING MAX(status) = 1

Regards,
Sundari
Re: date function [message #355223 is a reply to message #355148] Thu, 23 October 2008 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> def month=10
SQL> def year=2008
SQL> with 
  2    lines as (
  3      select 7*(level-1) d
  4      from dual 
  5      connect by level <= to_number(to_char(last_day(to_date('&month&year','MMYYYY')),'WW'))
  6                          - to_number(to_char(to_date('&month&year','MMYYYY'),'WW'))
  7    )
  8  select to_char(to_date('&month&year','MMYYYY')+d,'YYYYQMM') R1,
  9         to_char(to_date('&month&year','MMYYYY')+d,'YYYYWW') R2
 10  from lines
 11  /
R1      R2
------- ------
2008410 200840
2008410 200841
2008410 200842
2008410 200843

4 rows selected.

Regards
Michel
Re: date function [message #355332 is a reply to message #355211] Thu, 23 October 2008 14:31 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sundha wrote on Thu, 23 October 2008 03:43
Hi Konark,


The following query might be useful for you......

SELECT week_id,month_id
FROM
(SELECT week_id,month_id, DECODE(trim(TO_CHAR(date_id,'day')),'friday',1,0) status
FROM pk_date WHERE date_id BETWEEN '01-NOV-2008' AND '30-NOV-2008')
GROUP BY week_id,month_id
HAVING MAX(status) = 1



This is invalid code. PK_DATE is a column of DATE type. You are comparing a DATE column to a STRING. Error.
FOO SCOTT>l
  1* select 1 from dual where sysdate > '01-NOV-2008'
FOO SCOTT>/
select 1 from dual where sysdate > '01-NOV-2008'
                                   *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: date function [message #355375 is a reply to message #355332] Thu, 23 October 2008 19:25 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
Well ..whats the problem with
select week_id,month_id from t_date
where (date_id between '01-OCT-2008'and '31-OCT-2008' )
and trim(to_char(date_id, 'DAY')) = 'FRIDAY'


Its simple and not using those having and with clauses.
Re: date function [message #355389 is a reply to message #355375] Thu, 23 October 2008 22:32 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

konark wrote on Fri, 24 October 2008 05:55
Well ..whats the problem with
select week_id,month_id from t_date
where (date_id between '01-OCT-2008'and '31-OCT-2008' )
and trim(to_char(date_id, 'DAY')) = 'FRIDAY'


Its simple and not using those having and with clauses.




joy_division wrote on Fri, 24 October 2008

This is invalid code. PK_DATE is a column of DATE type. You are comparing a DATE column to a STRING. Error.




it will work if you convert date string to date and use Distinct in select query .

Smile
Rajuvan.
Re: date function [message #355489 is a reply to message #355375] Fri, 24 October 2008 08:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
konark wrote on Thu, 23 October 2008 20:25
Well ..whats the problem with
select week_id,month_id from t_date
where (date_id between '01-OCT-2008'and '31-OCT-2008' )
and trim(to_char(date_id, 'DAY')) = 'FRIDAY'


Its simple and not using those having and with clauses.


I already showed you. It is invalid code. What did you not understand? A million examples showing it works does not prove it is valid. Just one example of showing it does not work proves it is not valid.
FOO SCOTT>create table t_date (date_id date);

Table created.

FOO SCOTT> select * from t_date
  2 where (date_id between '01-OCT-2008'and '31-OCT-2008' )
  3 and trim(to_char(date_id, 'DAY')) = 'FRIDAY';

where (date_id between '01-OCT-2008'and '31-OCT-2008' )
                       *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: date function [message #355503 is a reply to message #355375] Fri, 24 October 2008 09:09 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@konark,

The code might work on your machine because of the implicit conversion used by Oracle. But this needn't be successful on every machine you put your code. I hope the following codes help you understand the things pointed out by joy_division and others.

*** Borrowing codes from joy_division:

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 24 19:19:31 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter session set nls_date_format='MM/DD/YYYY';

Session altered.

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
----------
10/24/2008

SQL> create table t_date (date_id date);

Table created.

SQL> SELECT *
  2  FROM   t_Date
  3  WHERE  (Date_Id BETWEEN '01-OCT-2008'
  4                          AND '31-OCT-2008')
  5         AND TRIM(To_char(Date_Id,'DAY')) = 'FRIDAY';
WHERE  (Date_Id BETWEEN '01-OCT-2008'
                        *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
-----------
24-OCT-2008

SQL>  SELECT *
  2   FROM   t_Date
  3   WHERE  (Date_Id BETWEEN '01-OCT-2008'
  4                           AND '31-OCT-2008')
  5          AND TRIM(To_char(Date_Id,'DAY')) = 'FRIDAY';

no rows selected


So the point is: Don't Rely on implicit conversion of Oracle too much. In fact, I use explicit conversion in my codes wherever possible.

Anyways, the logic given by @sundha was infact simple and easy.

Hope this helps, http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif

Regards,
Jo
Previous Topic: Columns into Rows Thru ORACLE SQL
Next Topic: How to avoid ovelapping dates
Goto Forum:
  


Current Time: Sun Dec 04 19:06:23 CST 2016

Total time taken to generate the page: 0.05917 seconds