Date:         Tue, 19 Dec 2006 08:09:43 -0800
Reply-To:     "Pardee, Roy" <pardee.r@GHC.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Pardee, Roy" <pardee.r@GHC.ORG>
Subject:      Re: Oracle pass-thru query gives "ORA-00933: SQL command not
              properly ended"
Comments: To: "Nordlund, Dan (DSHS/RDA)" <NordlDJ@dshs.wa.gov>
Content-Type: text/plain; charset="US-ASCII"

Thanks Dan!  I'll give that a try.  I like the BETWEEN for its
readability, but it does have this problem.  I suppose the other thing I
could do is figure out how to specify the hours/minutes/seconds and
include that in my endpoint...

Thanks also to Srinivas Kamireddi, who helpfully consulted w/me
off-list.

Cheers,

-Roy

-----Original Message-----
From: Nordlund, Dan (DSHS/RDA) [mailto:NordlDJ@dshs.wa.gov]
Sent: Monday, December 18, 2006 5:17 PM
To: Pardee, Roy; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Re: Oracle pass-thru query gives "ORA-00933: SQL command
not properly ended"



Daniel J. Nordlund
Research and Data Analysis
Washington State Department of Social and Health Services Olympia, WA
98504-5204


> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> Pardee, Roy
> Sent: Monday, December 18, 2006 1:46 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Oracle pass-thru query gives "ORA-00933: SQL command not
> properly ended"
>
> Ah--there it is!  Many thanks!
>
> So let me try everyone's patience w/a follow-up question--for this bit
> here:
>
>   where AdmitDt between to_date('01-jan-2003') AND
>                         to_date('31-dec-2003')
>
> My admit dates have time portions specified on them.  If I understand
> this right, I will not actually get admits happening after midnight on

> 31-dec-2003, which I want.  This is apparently acceptable to oracle:
>
>   where AdmitDt between to_date('01-jan-2003') AND
>                        (to_date('31-dec-2003') + .999)
>
> Does that do what I want?  Is there a better way?
>
> Thanks!
>
> -Roy
>

Roy,

I see you have received at least one reply to this.  I frequently need
to work with datetime values that come from oracle with a "default" time
value of 0:0:0 and compare them to values other than midnight.  I almost
never use BETWEEN because the endpoints are included as part of the
interval.  You therefore run into the problem that you are having.  I
usually code something like

   where (to_date('01-jan-2003') <= AdmitDt < (to_date('01-jan-2004');

i.e. use a strictly less than comparison on the day after your ending
point.

Hope this is helpful,

Dan

Daniel J. Nordlund
Research and Data Analysis
Washington State Department of Social and Health Services Olympia, WA
98504-5204