Date:         Wed, 26 Dec 2007 22:56:56 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: TO STORE CURRENT DATE AND TIME IN SQL SERVER DATABASE

On Wed, 26 Dec 2007 18:32:44 +0000, Paul Dorfman <sashole@BELLSOUTH.NET> wrote:

>Raghu,
>
>Use
>
>"%sysfunc (datetime(), datetime)"
>
>instead of
>
>"&SYSDATE &SYSTIME". If the SQL Server should refuse to accept the double
quotes (some RDBMS do) then use
>
>%str(%')%sysfunc (datetime(), datetime)%str(%')
>
>instead.
>
>Kind regards
>------------
>Paul Dorfman
>Jax, FL
>------------
>

From what Raghu has indicated, it appears that SQL Server is reached via a
LIBNAME engine. There is nothing to suggest use of SQL pass-through.

So, I think SAS rather than SQL Server is going to eat those quotes, be they
single or double.

But the third column name is specified with a name not valid in SAS. I
wonder how that is supposed to work.

All I can say is that if the problem is just with the values of the macro
variables for date and time, the solution is to replace the automatic ones
with user-defined ones, as in:

   proc sql;

   %let nowdate = %sysfunc(today(),date7.);
   %let nowtime = %sysfunc( time(),time5.);

   insert into AAA.BBB (x,y, Current Date&time,Desc)
    VALUES ('annie', 'p', "&nowdate &nowtime", 'name');

   quit;

>
>-------------- Original message ----------------------
>From: "raghur6@gmail.com" <raghur6@GMAIL.COM>
>>
>> proc sql;
>>         insert into AAA.BBB (x,y, Current Date&time,Desc)
>> VALUES ('annie', 'p', "&SYSDATE &SYSTIME", 'name');
>> quit;
>>
>> I am trying to insert the values from SAS to SQL Server Database AAA
>> and table BBB the following values they are working fine the only
>> problem is with Date&Time. When I am using &SYSDATE &SYSTIME and run
>> the application the time stored is the time when I opened the SAS
>> Session not the current time
>>
>> EX: if I open SAS at 11.30 Am and run the project at 11.50 AM
>> The value stored in the table should be 11.50.00:00 but it is stored
>> as 11.30 00:00... can any one let me know how to get rid of this
>> problem.
>> I want to store Minutes and Seconds as well in the Table
>>
>> Thanks,
>> Raghu