Date:         Fri, 4 Jan 2008 13:53:12 +0000
Reply-To:     Ari Toikka <toikkari@YAHOO.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ari Toikka <toikkari@YAHOO.CO.UK>
Subject:      Re: Data types going to SQL Server
Comments: To: Ed Heaton <EdHeaton@WESTAT.COM>
Content-Type: text/plain; charset=utf-8

Hi,

it seems to me that you are trying to
update SQL Server datetime column
with SAS date value.

SAS date value can be changed to 
datetime value with dhms-function:
    dt = dhms(d,0,0,0);
This adds the time component to the date.

Ari Toikka
Statistics Finland






----- Original Message ----
From: Ed Heaton <EdHeaton@WESTAT.COM>
To: SAS-L@LISTSERV.UGA.EDU
Sent: Thursday, 3 January, 2008 10:31:45 PM
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


      __________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com