Date:         Wed, 12 May 2004 10:17:08 -0400
Reply-To:     Francis Harvey <HARVEYF1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Francis Harvey <HARVEYF1@WESTAT.COM>
Subject:      Re: Insert into MS SQL 2000 by using SAS
Comments: To: SAS-L@LISTSERV.VT.EDU
Content-Type: text/plain

Loi,

I would rely on SQL Server to do the date conversion for you by giving
it an international date string. For instance, I use this code:

"'" || put(datepart(TimeStamp),yymmdd10.) || " " ||
   put(timepart(TimeStamp),time8.) || "'"

to give me a string in the form:

'2004-05-12 10:15:00'

which is happily converted by SQL Server into a datetime value during
the insert.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1@westat.com


> -----Original Message-----
> From: Loi Tan Vo [mailto:loi.tan.vo@FNH.NO]
> Sent: Wednesday, May 12, 2004 7:52 AM
> To: SAS-L@LISTSERV.VT.EDU
> Subject: Re: Insert into MS SQL 2000 by using SAS
>
>
> Hi Chang,
>
> I have tried your suggestion. But it did not work.
>
> Thanks anyway
>
> - Loi -
>
> chang_y_chung@HOTMAIL.COM (Chang Y. Chung) wrote in message
> news:<200405101254.i4ACs0O16197@listserv.cc.uga.edu>...
> > On Mon, 10 May 2004 02:28:24 -0700, Loi Tan Vo
> <loi.tan.vo@FNH.NO> wrote:
> >
> > >Hi all,
> > >
> > >I have a odbc connecting to MS SQL server 2000 and want by
> using SAS
> > >data step to insert data into a table in MS SQL. For ex.
> > >
> > >Proc sql;
> > >INSERT INTO sqllib.Table1(id, Name, Fromdate, tildate)
> > >VALUES(2,'statistic_1','01/01/2004'd,'31/01/2004'd);
> > >run;
> > >
> > >Then I got the followings errors
> > >ERROR: Invalid date/time/datetime constant '01/01/2004'd
> > >ERROR: Invalid date/time/datetime constant '31/01/2004'd
> > >
> > >How to convert datetime so that the ms sql can understand the
> > >datetime?
> > >
> > >Thanks in advance
> > >
> > Hi, Loi,
> >
> > Try #01/01/2004# instead of '01/01/2004'd.
> >
> > Cheers,
> > Chang
>