Date:         Fri, 4 Jan 2008 08:49:01 -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: Sigurd Hermansen <HERMANS1@westat.com>
In-Reply-To:  <CA8F89971ADA9F47A6C915BA2397844207B42335@MAILBE2.westat.com>
Content-Type: text/plain; charset="us-ascii"

I'm sorry.  I thought I was clear when I submitted this.  Now I see I
wasn't.

My values are SAS dates.  For instance...

05NOV2004 in SAS gets to SQL Server as 01JAN1960:04:33:00.000
29APR2006 ...                          01JAN1960:04:42:00.000
08JUL2004 ...                          01JAN1960:04:31:00.000
26OCT2006 ...                          01JAN1960:04:45:00.000
Etc.

Now, I know what's happening.  I'm just looking for a dataset option or
something like that to keep me from having to convert the SAS date
values to SAS datetime values everywhere before I move them off to SQL
Server.

The problem is that some of the values in SQL Server are correct and
some are erroneously put to January 1, 1960.  That is, when the data
gets to SAS, some are really datetime values and some are date values
masquerading as datetime values.  So, if I need to convert the value to
a datetime value, I will need to code something like...

    If (
        LScanDt lt '02JAN1960:00:00:00'dt
    ) then LScanDt = dhms( LScanDt , 0 , 0 , 0 ) ;

You know, the more I look at this, the more I come to realize that I'm
just going to have to do this to clean up the data.

However, I would still - for production - like to be able to write a
date value to SQL Server without converting it to a datetime value
first.


Ed

Ed Heaton
TB-286
#4818



-----Original Message-----
From: Sigurd Hermansen
Sent: Thursday, January 03, 2008 6:28 PM
To: Ed Heaton
Subject: RE: Data types going to SQL Server


Ed:
What result are you trying to produce? 01JAN1960:00:00:00.000 ? S

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Ed Heaton
Sent: Thursday, January 03, 2008 3: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