Date:         Wed, 12 Mar 2008 22:54:02 -0400
Reply-To:     Nirmal kumar <lazybone2k@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nirmal kumar <lazybone2k@GMAIL.COM>
Subject:      Re: Datetime in PROC SQL with SQLSERVER database
Comments: To: "Howard Schreier <hs AT dc-sug DOT org>"
          <schreier.junk.mail@gmail.com>
In-Reply-To:  <200803130248.m2CMiqIr012592@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

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?
> >>
>