LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (January 2008, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page