```Date: Thu, 2 Sep 2004 09:15:31 -0700 Reply-To: "Choate, Paul@DDS" Sender: "SAS(r) Discussion" From: "Choate, Paul@DDS" Subject: SAS date and time trivia - was RE: deleting records from MS Acce ss table Comments: To: "Miller, Jeremy T." Hi Jeremy - RE "SAS even puts it at 1960" - this is what SAS will do to a sasdate that is formatted as a SAS time. You need to convert your dates to datetime or visa versa: Datetime= dhms(date,0,0,0); Or Date=datepart(datetime); Cosider this: 43 data _null_; 44 date1=date(); 45 date2=date(); 46 date3=date(); 47 datetime1=datetime(); 48 datetime2=datetime(); 49 datetime3=datetime(); 50 51 format date2 datetime2 date.; 52 format date3 datetime3 datetime.; 53 54 put (_all_) (=/); 55 56 run; date1=16316 date2=02SEP04 date3=01JAN60:04:31:56 <<<"SAS even puts it at 1960"<<< datetime1=1409732503.6 datetime2=******* datetime3=02SEP04:08:21:44 A date is the number of days since 1960, so today is 16,316. A datetime is the number of seconds from 1960, so the second I ran this was 1,409,732,503. The datetime number was too large for SAS to understand as a date so it gives "*******". Dates in SAS fail on the 6589336th day after 1/1/60, which is 1/1/20001: 226 data _null_; 227 format date date.; 228 do i = 6589331 to 6589341 by 1; 229 date=i; 230 year=year(date); 231 put _all_; 232 end; 233 run; date=27DEC00 i=6589331 year=20000 _ERROR_=0 _N_=1 date=28DEC00 i=6589332 year=20000 _ERROR_=0 _N_=1 date=29DEC00 i=6589333 year=20000 _ERROR_=0 _N_=1 date=30DEC00 i=6589334 year=20000 _ERROR_=0 _N_=1 date=31DEC00 i=6589335 year=20000 _ERROR_=0 _N_=1 NOTE: Invalid argument to function YEAR at line 230 column 10. date=******* i=6589336 year=. _ERROR_=1 _N_=1 NOTE: Invalid argument to function YEAR at line 230 column 10. date=******* i=6589337 year=. _ERROR_=1 _N_=1 NOTE: Invalid argument to function YEAR at line 230 column 10. date=******* i=6589338 year=. _ERROR_=1 _N_=1 NOTE: Invalid argument to function YEAR at line 230 column 10. date=******* i=6589339 year=. _ERROR_=1 _N_=1 NOTE: Invalid argument to function YEAR at line 230 column 10. date=******* i=6589340 year=. _ERROR_=1 _N_=1 NOTE: Invalid argument to function YEAR at line 230 column 10. date=******* i=6589341 year=. _ERROR_=1 _N_=1 date=******* i=6589342 year=. _ERROR_=1 _N_=1 Certain date formats fail on 1/1/10000. SAS times fail at the same point, 569,318,630,400 seconds past 1/1/1960, which is 1/1/20001. 466 data _null_; 467 format datetime datetime.; 468 do i = 569318630398 to 569318630402 by 1; 469 datetime=i; 470 year=year(datepart(datetime)); 471 put _all_; 472 end; 473 run; datetime=31DEC00:23:59:58 i=569318630398 year=20000 _ERROR_=0 _N_=1 datetime=31DEC00:23:59:59 i=569318630399 year=20000 _ERROR_=0 _N_=1 NOTE: Invalid argument to function DATEPART at line 470 column 15. datetime=**************** i=569318630400 year=. _ERROR_=1 _N_=1 NOTE: Invalid argument to function DATEPART at line 470 column 15. datetime=**************** i=569318630401 year=. _ERROR_=1 _N_=1 NOTE: Invalid argument to function DATEPART at line 470 column 15. datetime=**************** i=569318630402 year=. _ERROR_=1 _N_=1 datetime=**************** i=569318630403 year=. _ERROR_=1 _N_=1 I don't know why this is so, but there may be a good reason. Maybe one of the many SAS-L illuminati can say. Any takers? Regards - Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Miller, Jeremy T. Sent: Wednesday, September 01, 2004 8:07 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: deleting records from MS Access table .... What I was going to do, was output a few temp files and use DBMS Copy to replace the files, but I'm getting a huge problem with the dates. The dates arrive as MMDDYYYY, which SAS has no problems reading. But, the transfer to Access changes them to DATETIME19.--and that makes all the dates 1960--I guess ATLAS shrugged. Even if I format the date in the temp SAS file to DATETIME19., SAS even puts it at 1960. I figured that SAS would have the date correctly and just leave the time at 12 am. I've tried a lot of different things here, and nothing seems to work. Funny though, the table in ACCESS I want to append to was originally created with SAS using EXPORT. I think ACCESS must store all of its dates as a datetime. ... ```

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