Date:         Fri, 4 Jan 2008 09:00:31 -0500
Reply-To:     Ed Heaton <EdHeaton@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ed Heaton <EdHeaton@WESTAT.COM>
Subject:      Re: Data types going to SQL Server
Comments: To: Ari Toikka <toikkari@yahoo.co.uk>
In-Reply-To:  <397943.47951.qm@web25509.mail.ukl.yahoo.com>
Content-Type: text/plain; charset="us-ascii"

Ari;
 
This project has quite a few date fields, lots of code that uses them,
and the data keep going from SQL Server to SAS and back to SQL Server.
I was really looking for a cleaner solution than to code the conversion
everywhere.
 
However, it's looking more and more like I won't get my wishes.
 

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
<http://www.westat.com/> 



-----Original Message-----
From: Ari Toikka [mailto:toikkari@yahoo.co.uk] 
Sent: Friday, January 04, 2008 8:53 AM
To: Ed Heaton; SAS-L@LISTSERV.UGA.EDU
Subject: Re: Data types going to SQL Server


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
<http://www.westat.com/> 



________________________________

Sent from Yahoo!
<http://us.rd.yahoo.com/mailuk/taglines/isp/control/*http://us.rd.yahoo.
com/evt=51949/*http://uk.docs.yahoo.com/mail/winter07.html>  - a smarter
inbox.