Date:         Wed, 12 Mar 2008 22:48:24 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Subject:      Re: Datetime in PROC SQL with SQLSERVER database

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