Date:         Thu, 13 Mar 2008 09:48:38 -0400
Reply-To:     Mike Rhoads <RHOADSM1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Rhoads <RHOADSM1@WESTAT.COM>
Subject:      Re: Datetime in PROC SQL with SQLSERVER database
Comments: To: Nirmal kumar <lazybone2k@gmail.com>
In-Reply-To:  <601d11620803121954l9327865r3415c53646b15aaa@mail.gmail.com>
Content-Type: text/plain; charset="us-ascii"

Kumar,

Try:

%let s_date = '20080229';

and then

where a.expiration_date > CONVERT(DATETIME,&s_date,101)

SQL Server actually accepts date constants in a wide variety of formats,
but they recommend this one as the most fool-proof, as it should work
regardless of language setting, etc.

Note that this result INCLUDES results from Feb. 29 (as they are "after"
the first second of that date).  So you may need to use >= and specify
the first date whose records you actually want.

HTH!

Mike Rhoads
Westat
RhoadsM1@Westat.com

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Nirmal kumar
Sent: Wednesday, March 12, 2008 10:54 PM
To: Howard Schreier <hs AT dc-sug DOT org>
Cc: SAS-L@listserv.uga.edu
Subject: Re: Datetime in PROC SQL with SQLSERVER database


Yes Sir,

I completely agree with what you said. I do have a solution with the SAS
datepart function and if statement. I thought I would ask this question
in
the forum and find a better answer.

Well....anyways thanks for ur input and suggestion.

Cheers,
Kumar


On 3/12/08, Howard Schreier <hs AT dc-sug DOT org> <
schreier.junk.mail@gmail.com> wrote:
>
> On Wed, 12 Mar 2008 22:11:52 -0400, Nirmal kumar
<lazybone2k@GMAIL.COM>
> wrote:
>
> >Hi all,
> >
> >Howard:
> >I am using ODBC to connect to the database. Thanks
>
> Then your questions are really for SQL SERVER experts rather than SAS
> experts.
>
> >
> >Sigurd:
> >
> >I tried using datepart function in the SQL where statement. It says
that
> >datepart function needs to have two arguments and it is invalid with
one
> >argument.
>
> The *SAS* DATEPART function uses one argument). But that's irrelevant;
you
> are writing code for SQL SERVER, which apparently has a DATEPART
function
> whose rules you must follow.
>
> >
> >I also converted the date in the macro variable to datetime22.3
function
> and
> >then used in the where statement.
>
> This is the SAS part: forming a literal which SQL SERVER will
understand.
>
> >Even then i am getting the following
> >error.
> >
> >ERROR: CLI open cursor error: [Microsoft][ODBC SQL Server Driver][SQL
> >Server]Arithmetic
> >       overflow error converting expression to data type
smalldatetime.
> >
> >Any help on this is really appreciated.
> >
> >thanks,
> >
> >cheers,
> >Kumar
> >
> >
> >
> >On 3/12/08, Howard Schreier <hs AT dc-sug DOT org> <
> >schreier.junk.mail@gmail.com> wrote:
> >>
> >> On Wed, 12 Mar 2008 18:49:37 -0400, SAS-L Nirmal
<lazybone2k@GMAIL.COM>
> >> wrote:
> >>
> >> >Hi SAS-Listers,
> >> >
> >> >I have a problem in hand and also I have a solution but i am
looking
> for
> >> >something better. Please help me with your suggestions and tips.
> >> >
> >> >I have a date in the SQLSERVER as a smalldatetime and i have to do
the
> >> >data extraction by setting a condition on the date. I have the
> required
> >> >information in a macro variable. In my PROC SQL, I used the where
> >> >statement as follows
> >> >
> >> >where a.expiration_date > &s_date
> >> >
> >> >
> >> >The querry runs without any problem, but not as I wanted. The
> expiration
> >> >date is in the datetime22.3 and the macro date is in date9. . Well
> >> >datetime22.3 in numeric format is always higher than the date9. in
> >> numeric
> >> >format. so the condition is always true. I want a solution so that
i
> can
> >> >eliminate it right here.
> >> >
> >> >Teh current solution i have is to use the datepart function in the
> >> >subsequent data step and use a if statement to delete all the
unwanted
> >> >observations like as follows:
> >> >
> >> >if datepart(expiration_date) < &s_date then delete;
> >> >
> >> >This things works fine. But is there a way in which i can
eliminate
> the
> >> if
> >> >statement and the set the condition in the proc sql itself.
> >> >
> >> >Any help on this is really appreciated.
> >> >
> >> >Thanks,
> >> >
> >> >Cheers,
> >> >Kumar
> >>
> >> Is this pass-thru SQL or are you using a LIBNAME statement to point
to
> SQL
> >> SERVER?
> >>
>