Home » SQL & PL/SQL » SQL & PL/SQL » Date Function (Oralce 9i)
Date Function [message #395617] Thu, 02 April 2009 01:25 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I am trying to pull out the data where the date is greater than 12-Jan-2009 and less than 14-Jan-2009 but i am getting wrong output. The Date format in the Table is 'YYYYMMDD'. I need to give date condition in the 'DD-MON-YYYY' format. Please give me correct query and explain why it is giving different output.

SQL> select a.patient, to_char(to_date(a.dci_date,'yyyymmdd'),'DD-MON-YYYY') DCI_Date, b.name
  2  from received_dcis a, dcis b
  3  where a.clinical_study_id=8313
  4  and a.dci_id=b.dci_id
  5  and a.received_dci_status_code<> 'REMOVED'
  6  and to_char(to_date(a.dci_date,'yyyymmdd'),'DD-MON-YYYY') >= '12-JAN-2009' 
  7  and to_char(to_date(a.dci_date,'yyyymmdd'),'DD-MON-YYYY') <= '14-JAN-2009';

PATIENT    DCI_DATE    NAME
---------- ----------- ------------------------------
09030011   13-SEP-2006 P001_PS
51040007   14-FEB-2006 P001_PS
12010009   14-FEB-2006 P001_PS
52030012   14-FEB-2006 P001_PS
44010021   14-FEB-2006 P001_PS
11010009   14-FEB-2006 P001_PS
11010005   14-FEB-2006 P001_PS
11010004   14-FEB-2006 P001_PS
02010028   14-FEB-2006 P001_PS
01010012   14-FEB-2006 P001_PS
41010011   14-FEB-2006 P001_PS

PATIENT    DCI_DATE    NAME
---------- ----------- ------------------------------
18030004   14-FEB-2006 P001_PS
44010015   14-FEB-2006 P001_PS
09010037   13-DEC-2005 P001_PS
12050022   14-DEC-2005 P001_PS
12030017   14-DEC-2005 P001_PS
15060007   14-DEC-2005 P001_PS
15060006   14-DEC-2005 P001_PS
09040022   14-DEC-2005 P001_PS
12050021   14-DEC-2005 P001_PS
12050028   14-DEC-2005 P001_PS
12050033   14-DEC-2005 P001_PS
Re: Date Function [message #395619 is a reply to message #395617] Thu, 02 April 2009 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see anything wrong in your output, it does what you say:
string '13-SEP-2006' is indeed between string '12-JAN-2009' and string '14-JAN-2009'.

If you want to work on dates give dates not strings.

Regards
Michel
Re: Date Function [message #395620 is a reply to message #395617] Thu, 02 April 2009 01:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> The Date format in the Table is 'YYYYMMDD'.
No.
DATE date type have no format.
DATES can be displayed in any format desired.
Re: Date Function [message #395622 is a reply to message #395619] Thu, 02 April 2009 01:45 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Please give me the correct query. I need to pull out the data where date is greater than 12-Jan-2009 and less than 14-Jan-2009. Here is the Table description, actually DCI_DATE is in VARCHAR2 data type. Any one help me to retrieve the correct data.

SQL> desc received_dcis;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 RECEIVED_DCI_ID                           NOT NULL NUMBER(10)
 RECEIVED_DCI_ENTRY_TS                     NOT NULL DATE
 DCI_ID                                    NOT NULL NUMBER(10)
 ENTERED_BY                                NOT NULL VARCHAR2(30)
 END_TS                                    NOT NULL DATE
 CLINICAL_STUDY_ID                         NOT NULL NUMBER(10)
 INVESTIGATOR_ID                           NOT NULL NUMBER(10)
 INVESTIGATOR                              NOT NULL VARCHAR2(10)
 SITE_ID                                   NOT NULL NUMBER(10)
 SITE                                      NOT NULL VARCHAR2(10)
 PATIENT_POSITION_ID                       NOT NULL NUMBER(10)
 PATIENT                                   NOT NULL VARCHAR2(10)
 ACTUAL_EVENT_ID                           NOT NULL NUMBER(10)
 RECEIVED_DCI_STATUS_CODE                  NOT NULL VARCHAR2(15)
 LOG_IN_TS                                 NOT NULL DATE
 DOCUMENT_NUMBER                           NOT NULL VARCHAR2(20)
 DATA_LOCK_FLAG                            NOT NULL VARCHAR2(1)
 ACCESSIBLE_TS                             NOT NULL DATE
 BLANK_FLAG                                NOT NULL VARCHAR2(1)
 CLIN_PLAN_EVE_ID                          NOT NULL NUMBER(10)
 CLIN_PLAN_EVE_NAME                        NOT NULL VARCHAR2(20)
 SUBEVENT_NUMBER                           NOT NULL NUMBER(2)
 DCI_DATE                                           VARCHAR2(8)
 DCI_TIME                                           VARCHAR2(6)
 DATA_FILE_ID                                       NUMBER(10)
 DATA_LOCK_TS                                       DATE
 LAST_STATUS_CHANGE_TS                              DATE
 LAST_NEW_VERSION_TS                                DATE
 COMMENT_TEXT                                       VARCHAR2(200)
 IMAGE_DOCUMENT_NUMBER                              VARCHAR2(20)
 IMAGE_INDEX_DATE                                   DATE
 MODIFICATION_TS                                    DATE
 MODIFIED_BY                                        VARCHAR2(30)
 VISIT_NUMBER                              NOT NULL NUMBER(5)
 FIRST_BOOK_PAGE                                    VARCHAR2(15)
 NUMBER_OF_PAGES                                    NUMBER(4)
 DCI_BOOK_ID                                        NUMBER(10)
 LAST_CHANGE_TS                                     DATE

[Updated on: Thu, 02 April 2009 01:56]

Report message to a moderator

Re: Date Function [message #395628 is a reply to message #395617] Thu, 02 April 2009 01:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
First you need to know how to spell SQL.
Re: Date Function [message #395630 is a reply to message #395628] Thu, 02 April 2009 01:59 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
First you need to know how to spell SQL? what's wrong? where i am doing wrong?
Re: Date Function [message #395632 is a reply to message #395630] Thu, 02 April 2009 02:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You want to compare two dates. Then why do you convert them to strings?
You should convert the string (the input) to a date and then compare. Remember to use an explicit date-format when doing the to_date.
Re: Date Function [message #395654 is a reply to message #395630] Thu, 02 April 2009 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ygsunilkumar wrote on Thu, 02 April 2009 08:59
First you need to know how to spell SQL? what's wrong? where i am doing wrong?

Michel Cadot wrote on Thu, 02 April 2009 08:33
I don't see anything wrong in your output, it does what you say:
string '13-SEP-2006' is indeed between string '12-JAN-2009' and string '14-JAN-2009'.

If you want to work on dates give dates not strings.

Regards
Michel

Re: Date Function [message #395735 is a reply to message #395654] Thu, 02 April 2009 08:48 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
you try as follows.

and to_date(a.dci_date,'yyyymmdd') >= '12-JAN-2009' 
and to_date(a.dci_date,'yyyymmdd') <= '14-JAN-2009';


regards,
Delna
Re: Date Function [message #395737 is a reply to message #395735] Thu, 02 April 2009 08:50 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Wrong! You are comparing Dates to Strings

Not onlyu that, but you are converting a date into a date using the to_date Function which will convert the date into a string then back into a date again. Pointless and likely to cause errors.

[Updated on: Thu, 02 April 2009 08:51]

Report message to a moderator

Re: Date Function [message #395739 is a reply to message #395735] Thu, 02 April 2009 08:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Remember, when working with dates, when comparing dates, you should NOT have a string on either side of the operator!
Re: Date Function [message #395836 is a reply to message #395739] Thu, 02 April 2009 15:41 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
1. I can't believe Delna is STILL comparing strings to DATEs.

2. Frank and pabolee of course are making great points, but did you guys notice that DCI_DATE in the table is a (gasp!) VARCHAR?
Re: Date Function [message #395946 is a reply to message #395836] Fri, 03 April 2009 04:33 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Gurus,

I think, pablolee sir has not seen VARCHAR2 type of dci_date.

Quote:

, but did you guys notice that DCI_DATE in the table is a (gasp!) VARCHAR?



Yes joy_division sir,
That is why I converted that field into date
And I thought that RHS part of the condition will be converted to date automatically.
Is it so?

regards,
Delna
Re: Date Function [message #395950 is a reply to message #395946] Fri, 03 April 2009 04:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
delna.sexy wrote on Fri, 03 April 2009 11:33

And I thought that RHS part of the condition will be converted to date automatically.
Is it so?


Using what format-mask?
Re: Date Function [message #395970 is a reply to message #395946] Fri, 03 April 2009 06:16 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Delna,

Implicit conversion in RHS part will be only successful if you specify the date in the correct format mask. The following example might help you understand the difference.

SQL> set feedback on;
SQL> SELECT 1 FROM Dual
  2  where to_date('08/01/2009','DD/MM/YYYY') = '08/01/2009';
where to_date('08/01/2009','DD/MM/YYYY') = '08/01/2009'
                                           *
ERROR at line 2:
ORA-01843: not a valid month


SQL> SELECT sysdate from Dual;

SYSDATE
---------
03-APR-09

1 row selected.

SQL>  SELECT 1 FROM Dual
  2   where to_date('08/01/2009','DD/MM/YYYY') = '08-Jan-09';

         1
----------
         1

1 row selected.



Anyways the best way is not to depend on implicit conversions when it comes to Date. Use TO_Date Function on the String when you are comparing it against a date.

SQL> SELECT 1 FROM Dual
  2  where to_date('08/01/2009','DD/MM/YYYY') = to_date('08/01/2009','DD/MM/YYYY');

         1
----------
         1

1 row selected.


Hope this helps.

{***Added***]
Forgot to mention, Irrespective of RHS and LHS, whenever you are comparing character to date, Oracle will try to implicitly convert character data to date.

Regards,
Jo

[Updated on: Fri, 03 April 2009 06:27]

Report message to a moderator

icon7.gif  Re: Date Function [message #396098 is a reply to message #395970] Fri, 03 April 2009 20:10 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
It really helped me. Thanks you very much John.
Re: Date Function [message #396181 is a reply to message #395617] Sat, 04 April 2009 13:12 Go to previous messageGo to next message
ashleyora10
Messages: 1
Registered: April 2009
Junior Member
hi there,

I am a new member and new to this forum. I am new to oracle and sql. I have started oracle learning by myself for 2 weeks. I have just started sql self learning couple days ago. I do not know a lot of stuffs yet, but I would like to tell you my idea if it works for your case. I fixed your code as below:

SQL> select a.patient, to_char(a.dci_date,'DD-MON-YYYY') DCI_Date, b.name
from received_dcis a, dcis b
where a.clinical_study_id=8313
and a.dci_id=b.dci_id
and a.received_dci_status_code<> 'REMOVED'
and a.dci_date >= to_date('12-JAN-2009', 'dd-MON-yyyy')
and a.dci_date <= to_date('14-JAN-2009', 'dd-MON-yyyy');

Please let me know what you think. I would love to learn more from everybody.

My English is limit. I hope you understand me.

Thanks
Ashley



Re: Date Function [message #396342 is a reply to message #395617] Mon, 06 April 2009 03:58 Go to previous message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
@ashleyora10

you need to to_date a.dci_date in this case because although it's called dci_date the column is actually a varchar2.
Previous Topic: Retrieve data from rows into columns
Next Topic: check for null
Goto Forum:
  


Current Time: Wed Dec 07 11:05:28 CST 2016

Total time taken to generate the page: 0.26852 seconds