Date:         Mon, 5 Dec 2011 13:43:53 -0800
Reply-To:     "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Subject:      Re: Oracle SQL pass through to_date again
Comments: To: "mlhoward@avalon.net" <mlhoward@avalon.net>
In-Reply-To:  <20111205133011.2DA6471@resin04.mta.everyone.net>
Content-Type: text/plain; charset="utf-8"

Thanks Mary,

I tried but got this:
to_date(b.date_completed,'YYYYMMDD') between to_date('20071231', 'YYYYMMDD') and
                         to_date('20080106', 'YYYYMMDD')

ERROR: ORACLE execute error: ORA-01840: input value not long enough for date format.

I think this is caused by those partial date like '2007','200605' etc.

In SAS I can use ?? with informat to suppress the message, can I do something
similar in Oracle?

I tried rpad:

to_date(rpad(b.date_completed,8,0),'YYYYMMDD') between ....

But got this

ERROR: ORACLE execute error: ORA-01843: not a valid month.

Make sense, since 20070000 has an invalid month.


-----Original Message-----
From: Mary [mailto:mlhoward@avalon.net]
Sent: Monday, December 05, 2011 1:30 PM
To: Huang, Ya
Cc: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Oracle SQL pass through to_date again

Ya,

you could use the to_date function on multiple sides:

to_date(dtl_fdos,'yyyymmdd') between to_date('01/01/2007','mm/dd/yyyy') and to_date('12/31/2011','mm/dd/yyyy');

-Mary

--- ya.huang@AMYLIN.COM wrote:

From: Ya Huang <ya.huang@AMYLIN.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject:      Re: Oracle SQL pass through to_date again
Date:         Mon, 5 Dec 2011 16:22:57 -0500

What if I want to subset only based on full date, like something I
can do in SAS:

'31DEC2007'd <= input(date_completed,??yymmdd8.) <= '06JAN2008'd

But do it in Oracle?



On Mon, 5 Dec 2011 15:45:17 -0500, Tom Abernathy <tom.abernathy@GMAIL.COM>
wrote:

>YYYYMMDD is a useful format for partial dates.
>
> date_completed >= '2007' and date_complete <= '20080106'
>
>Should match your original attempt to get dates between
>'01JAN2007'd and '06JAN2008'd.
>
>
>On Mon, 5 Dec 2011 12:24:05 -0800, Huang, Ya <Ya.Huang@AMYLIN.COM> wrote:
>
>>Thanks,
>>
>>Now I know it is a character field:
>>
>>Obs    COLUMN_NAME                      TYPE        LENGTH    NULLABL
>>
>>   1    CASE_ID                          VARCHAR2      500        N
>>   8    DATE_COMPLETED                   VARCHAR2        8        Y
>>
>>
>>I also found that date_completed could have partial date like '2007',
>'200710' etc.
>>
>>So, what the best way to select the records based on such a character
>column and
>>a specified time period?
>>
>...
>>>>On Mon, Dec 5, 2011 at 11:23 AM, Ya Huang <ya.huang@amylin.com> wrote:
>>>>> I thought it would work too, but somehow it didn't. There is nothing
>>>>> leading to this, this is the first step:
>>>>>
>>>>> 75         proc sql;
>>>>> 76         connect to oracle(path=&path user=&userid pass=&pwd
>>>>> dbmax_text=25000);
>>>>> 77          create table here.aepccase as
>>>>> 78          select *
>>>>> 79          from connection to oracle
>>>>> 80          (select *
>>>>> 81          from %aepccase
>>>>> MPRINT(AEPCCASE):   (select a.case_id, a.delete_flag, a.prod_cd,
>>>>> a.adverse_event_flag, a.product_complaint_flag, a.case_create_dt,
>>>>> b.date_completed from aers.ae_cases a, aers.pc_investigations b
>>where
>>>>> a.case_id = b.case_id and (upper(a.delete_flag) !='Y' or
>>>>> a.delete_flag is null) and upper(a.prod_cd) in ('XXX','YYY')
>>>>> and upper(a.ADVERSE_EVENT_FLAG)='Y' and
>>>upper(a.PRODUCT_COMPLAINT_FLAG)
>>>>> ='Y' and b.date_completed between to_date('20071231',
>>>>> 'YYYYMMDD') and to_date('20080106', 'YYYYMMDD') )
>>>>> 82           )
>>>>> 83         ;
>>>>> ERROR: ORACLE execute error: ORA-01861: literal does not match
>>format
>>>>> string.
>>>>> NOTE: Table HERE.AEPCCASE created, with 0 rows and 7 columns.
>...