LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (March 2008, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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? > >> >


Back to: Top of message | Previous page | Main SAS-L page