Date:         Thu, 3 Jan 2008 12:54:29 -0800
Reply-To:     "Pardee, Roy" <pardee.r@GHC.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Pardee, Roy" <pardee.r@GHC.ORG>
Subject:      Re: Data types going to SQL Server
Comments: To: Ed Heaton <EdHeaton@westat.com>
In-Reply-To:  <403593359CA56C4CAE1F8F4F00DCFE7D0C1E4700@MAILBE2.westat.com>
Content-Type: text/plain; charset="us-ascii"

Huh--strange.  I don't believe there is a pure date datatype in
mssql--at least not in 2000.  I don't think I've ever seen an rdbms w/a
plain date-no-time datatype...

-----Original Message-----
From: Ed Heaton [mailto:EdHeaton@westat.com]
Sent: Thursday, January 03, 2008 12:52 PM
To: Pardee, Roy; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Data types going to SQL Server

Roy;

Thanks, but I got the same result.  Didn't really think this would work
because I want the value to be a datetime value on the SQL Server
database, not a date value.

Ed

Ed Heaton
TB-286
#4818



-----Original Message-----
From: Pardee, Roy [mailto:pardee.r@ghc.org]
Sent: Thursday, January 03, 2008 3:48 PM
To: Ed Heaton; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Data types going to SQL Server


Try:

  dbtype = (lscandt = SQL_DATE)

Timestamp is a different beast in the rdbms world I believe...

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ed
Heaton
Sent: Thursday, January 03, 2008 12:32 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Data types going to SQL Server

Good afternoon, all;

I'm on a new project interacting more closely with SQL Server than I
ever had before.  I have this problem that I can't seem to solve.

I need to write a SAS date value to a SQL Server dateTime field.  The
numeric variable formatted with date9. is in work.foo and I'm trying to
write it to sqlLib.Ed.  If I use...

Data sqlLib.Ed ;
    Modify
        sqlLib.Ed
        foo( keep= CaseId SeqNum LScanDt )
    ;
    By CaseId SeqNum ;
Run ;

I get datetime values in SQL Server like...

01JAN1960:04:33:00.000
01JAN1960:04:42:00.000
01JAN1960:04:31:00.000
01JAN1960:04:45:00.000
01JAN1960:04:19:00.000
01JAN1960:04:41:00.000
01JAN1960:04:21:00.000

etc.  I expected that.  If I use...

Data sqlLib.Ed ;
    Modify
        sqlLib.Ed( dbType=( LScanDt=DBTYPE_DBTIMESTAMP ) )
        foo( keep= CaseId SeqNum LScanDt )
    ;
    By CaseId SeqNum ;
Run ;

I get the same result.  I'm not too surprised because I'm having
difficulity finding - with assurance - the value for the DBTYPE= option.


Data sqlLib.Ed ;
    Modify
        sqlLib.Ed( dbType=( LScanDt='datetime' ) )
        foo( keep= CaseId SeqNum LScanDt )
    ;
    By CaseId SeqNum ;
Run ;

That didn't work either.

Can anyone out there help me?  I've looked and looked in the
documentation with no luck.

Ed

Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation), 1650 Research
Boulevard, TB-286, Rockville, MD 20850-3195
Voice: (301) 610-4818               Fax: (301) 294-2085
mailto:EdHeaton@Westat.com          http://www.Westat.com