Changing Date Formats
Locked
 
|
When original dates come in as string, is there an easier way? STRING Day Month (A2) Remainder (A60) EndDateNew (A65). COMPUTE Day = CHAR.SUBSTR(EndDate,4,2). COMPUTE Month = CHAR.SUBSTR(EndDate,1,2). COMPUTE Remainder = CHAR.SUBSTR(EndDate,7,57). COMPUTE EndDateNew = CONCAT(Day, "/", Month, "/", Remainder). EXECUTE. ALTER TYPE EndDateNew (DATETIME22.0). LIST EndDate.
EndDate EndDateNew 09/11/2013 19:19:48 11-SEP-2013 19:19:48 09/11/2013 19:31:23 11-SEP-2013 19:31:23 09/11/2013 19:41:28 11-SEP-2013 19:41:28 09/11/2013 19:59:38 11-SEP-2013 19:59:38 09/11/2013 20:25:18 11-SEP-2013 20:25:18 09/11/2013 22:52:51 11-SEP-2013 22:52:51 09/12/2013 13:44:53 12-SEP-2013 13:44:53
Thx, Bob Walker Surveys & Forecasts, LLC |
|
|
Bob, did you try and reject due to errors reading the dates as datetime20? And, if that didn’t work, have you tried the Alter type command? Gene Maguin From: SPSSX(r) Discussion [mailto:[hidden email]]
On Behalf Of Bob Walker When original dates come in as string, is there an easier way? STRING Day Month (A2) Remainder (A60) EndDateNew (A65). COMPUTE Day = CHAR.SUBSTR(EndDate,4,2). COMPUTE Month = CHAR.SUBSTR(EndDate,1,2). COMPUTE Remainder = CHAR.SUBSTR(EndDate,7,57). COMPUTE EndDateNew = CONCAT(Day, "/", Month, "/", Remainder). EXECUTE. ALTER TYPE EndDateNew (DATETIME22.0). LIST EndDate.
EndDate EndDateNew 09/11/2013 19:19:48 11-SEP-2013 19:19:48 09/11/2013 19:31:23 11-SEP-2013 19:31:23 09/11/2013 19:41:28 11-SEP-2013 19:41:28 09/11/2013 19:59:38 11-SEP-2013 19:59:38 09/11/2013 20:25:18 11-SEP-2013 20:25:18 09/11/2013 22:52:51 11-SEP-2013 22:52:51 09/12/2013 13:44:53 12-SEP-2013 13:44:53
Thx, Bob Walker Surveys & Forecasts, LLC |
|
|
Hi Gene, Yup, tried ALTER TYPE… usually works without any tweaking, but in this case it reads the month and day in reverse. My file comes in as mm/dd/yyyy hh:mm:ss. ALTER TYPE then reads
09/11/2013 19:19:48 as
9-Nov-2013 19:19:48 instead of
11-Sep-2013 19:19:48. A date such as
09/22/2013 19:19:48 returns a missing value since there is no month “22”. So I disaggregated the string and reassembled it, which ultimately works with ALTER TYPE, but it seems to me that I must be doing something wrong. Thanks much, Bob Surveys & Forecasts, LLC From: SPSSX(r) Discussion [mailto:[hidden email]]
On Behalf Of Maguin, Eugene Bob, did you try and reject due to errors reading the dates as datetime20? And, if that didn’t work, have you tried the Alter type command? Gene Maguin From: SPSSX(r) Discussion [[hidden email]]
On Behalf Of Bob Walker When original dates come in as string, is there an easier way? STRING Day Month (A2) Remainder (A60) EndDateNew (A65). COMPUTE Day = CHAR.SUBSTR(EndDate,4,2). COMPUTE Month = CHAR.SUBSTR(EndDate,1,2). COMPUTE Remainder = CHAR.SUBSTR(EndDate,7,57). COMPUTE EndDateNew = CONCAT(Day, "/", Month, "/", Remainder). EXECUTE. ALTER TYPE EndDateNew (DATETIME22.0). LIST EndDate.
EndDate EndDateNew 09/11/2013 19:19:48 11-SEP-2013 19:19:48 09/11/2013 19:31:23 11-SEP-2013 19:31:23 09/11/2013 19:41:28 11-SEP-2013 19:41:28 09/11/2013 19:59:38 11-SEP-2013 19:59:38 09/11/2013 20:25:18 11-SEP-2013 20:25:18 09/11/2013 22:52:51 11-SEP-2013 22:52:51 09/12/2013 13:44:53 12-SEP-2013 13:44:53
Thx, Bob Walker Surveys & Forecasts, LLC |
|
|
In reply to this post by Maguin, Eugene
That will read the dates incorrectly because
datetime reads the date portion as d/m/y not m/d/y.
dataset close all. new file. data list free (",") /EndDate (a25). begin data 09/11/2013 19:19:48 end data. compute #date=number(substr(EndDate, 1, char.index(Enddate, " ")-1), adate10). compute #time=number(substr(EndDate, char.index(Enddate, " ")+1), time8). print /#time. compute EndDateNew=#date+#time. formats EndDateNew (datetime25). list. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: "Maguin, Eugene" <[hidden email]> To: [hidden email], Date: 09/20/2013 02:36 PM Subject: Re: Changing Date Formats Sent by: "SPSSX(r) Discussion" <[hidden email]> Bob, did you try and reject due to errors reading the dates as datetime20? And, if that didn’t work, have you tried the Alter type command? Gene Maguin From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Bob Walker Sent: Friday, September 20, 2013 3:28 PM To: [hidden email] Subject: Changing Date Formats When original dates come in as string, is there an easier way? STRING Day Month (A2) Remainder (A60) EndDateNew (A65). COMPUTE Day = CHAR.SUBSTR(EndDate,4,2). COMPUTE Month = CHAR.SUBSTR(EndDate,1,2). COMPUTE Remainder = CHAR.SUBSTR(EndDate,7,57). COMPUTE EndDateNew = CONCAT(Day, "/", Month, "/", Remainder). EXECUTE. ALTER TYPE EndDateNew (DATETIME22.0). LIST EndDate. EndDate EndDateNew 09/11/2013 19:19:48 11-SEP-2013 19:19:48 09/11/2013 19:31:23 11-SEP-2013 19:31:23 09/11/2013 19:41:28 11-SEP-2013 19:41:28 09/11/2013 19:59:38 11-SEP-2013 19:59:38 09/11/2013 20:25:18 11-SEP-2013 20:25:18 09/11/2013 22:52:51 11-SEP-2013 22:52:51 09/12/2013 13:44:53 12-SEP-2013 13:44:53 Thx, Bob Walker Surveys & Forecasts, LLC www.safllc.com |
Administrator
|
In reply to this post by Robert Walker
How about going for the jugular ;-)
COMPUTE dtnum=NUMBER(SUBSTR(EndDate,1,10),ADATE10) + NUMBER(SUBSTR(EndDate,9,8),TIME8). FORMATS dtnum (DATETIME20).
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Administrator
|
In reply to this post by Robert Walker
I don't know if this is any "easier" or not, but it is somewhat different from the method in Bob's original post. It avoids swapping the positions of mm and dd by using the ADATE10 format for the date part of the original variable.
NEW FILE. DATASET CLOSE all. data list list / EndDate(a19). begin data "09/11/2013 19:19:48" "09/11/2013 19:31:23" "09/11/2013 19:41:28" "09/11/2013 19:59:38" "09/11/2013 20:25:18" "09/11/2013 22:52:51" "09/12/2013 13:44:53" end data. string d(a10) t(a8). compute d = char.substr(EndDate,1,10). compute t = char.substr(EndDate,12,8). alter type d (adate10) t (time8). compute EndDateNew = d + t. formats EndDateNew(datetime22). * EXECUTE needed before DELETE VARIABLES. execute. delete variables d t. list. OUTPUT: EndDate EndDateNew 09/11/2013 19:19:48 11-SEP-2013 19:19:48 09/11/2013 19:31:23 11-SEP-2013 19:31:23 09/11/2013 19:41:28 11-SEP-2013 19:41:28 09/11/2013 19:59:38 11-SEP-2013 19:59:38 09/11/2013 20:25:18 11-SEP-2013 20:25:18 09/11/2013 22:52:51 11-SEP-2013 22:52:51 09/12/2013 13:44:53 12-SEP-2013 13:44:53
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
|
Administrator
|
Or you could do what David suggested! ;-)
It's essentially the same as my approach, but cuts it all down to 2 lines. COMPUTE EndDateNew = NUMBER(SUBSTR(EndDate,1,10),ADATE10) + NUMBER(SUBSTR(EndDate,9,8),TIME8). FORMATS EndDateNew (DATETIME20). LIST. OUTPUT: EndDate EndDateNew 09/11/2013 19:19:48 11-SEP-2013 13:19:19 09/11/2013 19:31:23 11-SEP-2013 13:19:31 09/11/2013 19:41:28 11-SEP-2013 13:19:41 09/11/2013 19:59:38 11-SEP-2013 13:19:59 09/11/2013 20:25:18 11-SEP-2013 13:20:25 09/11/2013 22:52:51 11-SEP-2013 13:22:52 09/12/2013 13:44:53 12-SEP-2013 13:13:44
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
|
|
Or this (untested):
begin program. import datetime def convertDate(value, fmt="%m/%d/%Y %H:%M:%S"): date = datetime.datetime.strptime(value, fmt) gregor = datetime.datetime(1582, 10, 14, 0, 0, 0) return (date - gregor).total_seconds() end program. spssinc trans result=endDateNew type=0 /formula "convertDate(endDate)". formats endDateNew (sdate10). /* or adate, edate... Regards, Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ----- Original Message ----- > From: Bruce Weaver <[hidden email]> > To: [hidden email] > Cc: > Sent: Friday, September 20, 2013 10:34 PM > Subject: Re: [SPSSX-L] Changing Date Formats > > /Or/ you could do what David suggested! ;-) > > It's essentially the same as my approach, but cuts it all down to 2 lines. > > COMPUTE EndDateNew = NUMBER(SUBSTR(EndDate,1,10),ADATE10) + > NUMBER(SUBSTR(EndDate,9,8),TIME8). > FORMATS EndDateNew (DATETIME20). > LIST. > > OUTPUT: > > EndDate EndDateNew > > 09/11/2013 19:19:48 11-SEP-2013 13:19:19 > 09/11/2013 19:31:23 11-SEP-2013 13:19:31 > 09/11/2013 19:41:28 11-SEP-2013 13:19:41 > 09/11/2013 19:59:38 11-SEP-2013 13:19:59 > 09/11/2013 20:25:18 11-SEP-2013 13:20:25 > 09/11/2013 22:52:51 11-SEP-2013 13:22:52 > 09/12/2013 13:44:53 12-SEP-2013 13:13:44 > > > > Bruce Weaver wrote >> I don't know if this is any "easier" or not, but it is > somewhat different >> from the method in Bob's original post. It avoids swapping the > positions >> of mm and dd by using the ADATE10 format for the date part of the original >> variable. >> >> NEW FILE. >> DATASET CLOSE all. >> >> data list list / EndDate(a19). >> begin data >> "09/11/2013 19:19:48" >> "09/11/2013 19:31:23" >> "09/11/2013 19:41:28" >> "09/11/2013 19:59:38" >> "09/11/2013 20:25:18" >> "09/11/2013 22:52:51" >> "09/12/2013 13:44:53" >> end data. >> >> string d(a10) t(a8). >> compute d = char.substr(EndDate,1,10). >> compute t = char.substr(EndDate,12,8). >> alter type d ( > * >> adate10 > * >> ) t (time8). >> compute EndDateNew = d + t. >> formats EndDateNew(datetime22). >> * EXECUTE needed before DELETE VARIABLES. >> execute. >> delete variables d t. >> list. >> >> OUTPUT: >> >> EndDate EndDateNew >> >> 09/11/2013 19:19:48 11-SEP-2013 19:19:48 >> 09/11/2013 19:31:23 11-SEP-2013 19:31:23 >> 09/11/2013 19:41:28 11-SEP-2013 19:41:28 >> 09/11/2013 19:59:38 11-SEP-2013 19:59:38 >> 09/11/2013 20:25:18 11-SEP-2013 20:25:18 >> 09/11/2013 22:52:51 11-SEP-2013 22:52:51 >> 09/12/2013 13:44:53 12-SEP-2013 13:44:53 >> >> Bob Walker wrote >>> Hi Gene, >>> >>> Yup, tried ALTER TYPE... usually works without any tweaking, but in > this >>> case it reads the month and day in reverse. >>> >>> My file comes in as mm/dd/yyyy hh:mm:ss. ALTER TYPE then reads > 09/11/2013 >>> 19:19:48 as 9-Nov-2013 19:19:48 instead of 11-Sep-2013 19:19:48. >>> >>> A date such as 09/22/2013 19:19:48 returns a missing value since there > is >>> no month "22". >>> >>> So I disaggregated the string and reassembled it, which ultimately > works >>> with ALTER TYPE, but it seems to me that I must be doing something > wrong. >>> >>> Thanks much, >>> >>> Bob >>> >>> Surveys & Forecasts, LLC >>> www.safllc.com<http://www.safllc.com/> >>> >>> >>> From: SPSSX(r) Discussion [mailto: > >>> SPSSX-L@.UGA > >>> ] On Behalf Of Maguin, Eugene >>> Sent: Friday, September 20, 2013 3:35 PM >>> To: > >>> SPSSX-L@.UGA > >>> Subject: Re: Changing Date Formats >>> >>> Bob, did you try and reject due to errors reading the dates as >>> datetime20? And, if that didn't work, have you tried the Alter type >>> command? >>> >>> Gene Maguin >>> >>> From: SPSSX(r) Discussion [mailto: > >>> SPSSX-L@.UGA > >>> ] On Behalf Of Bob Walker >>> Sent: Friday, September 20, 2013 3:28 PM >>> To: > >>> SPSSX-L@.UGA > >>> <mailto: > >>> SPSSX-L@.UGA > >>> > >>> Subject: Changing Date Formats >>> >>> When original dates come in as string, is there an easier way? >>> >>> STRING Day Month (A2) Remainder (A60) EndDateNew (A65). >>> COMPUTE Day = CHAR.SUBSTR(EndDate,4,2). >>> COMPUTE Month = CHAR.SUBSTR(EndDate,1,2). >>> COMPUTE Remainder = CHAR.SUBSTR(EndDate,7,57). >>> COMPUTE EndDateNew = CONCAT(Day, "/", Month, "/", > Remainder). >>> EXECUTE. >>> ALTER TYPE EndDateNew (DATETIME22.0). >>> LIST EndDate. >>> >>> EndDate >>> EndDateNew >>> >>> 09/11/2013 19:19:48 11-SEP-2013 >>> 19:19:48 >>> 09/11/2013 19:31:23 11-SEP-2013 >>> 19:31:23 >>> 09/11/2013 19:41:28 11-SEP-2013 >>> 19:41:28 >>> 09/11/2013 19:59:38 11-SEP-2013 >>> 19:59:38 >>> 09/11/2013 20:25:18 11-SEP-2013 >>> 20:25:18 >>> 09/11/2013 22:52:51 11-SEP-2013 >>> 22:52:51 >>> 09/12/2013 13:44:53 12-SEP-2013 >>> 13:44:53 >>> >>> Thx, >>> >>> Bob Walker >>> Surveys & Forecasts, LLC >>> www.safllc.com<http://www.safllc.com/> > > > > > > ----- > -- > Bruce Weaver > [hidden email] > http://sites.google.com/a/lakeheadu.ca/bweaver/ > > "When all else fails, RTFM." > > NOTE: My Hotmail account is not monitored regularly. > To send me an e-mail, please use the address shown above. > > -- > View this message in context: > http://spssx-discussion.1045642.n5.nabble.com/Changing-Date-Formats-tp5722147p5722153.html > Sent from the SPSSX Discussion mailing list archive at Nabble.com. > > ===================== > To manage your subscription to SPSSX-L, send a message to > [hidden email] (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD > ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|
|
In reply to this post by Bruce Weaver
Use scratch variables for variables you
don't need to save. Then you don't need to delete them later.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Bruce Weaver <[hidden email]> To: [hidden email], Date: 09/20/2013 03:31 PM Subject: Re: Changing Date Formats Sent by: "SPSSX(r) Discussion" <[hidden email]> I don't know if this is any "easier" or not, but it is somewhat different from the method in Bob's original post. It avoids swapping the positions of mm and dd by using the ADATE10 format for the date part of the original variable. NEW FILE. DATASET CLOSE all. data list list / EndDate(a19). begin data "09/11/2013 19:19:48" "09/11/2013 19:31:23" "09/11/2013 19:41:28" "09/11/2013 19:59:38" "09/11/2013 20:25:18" "09/11/2013 22:52:51" "09/12/2013 13:44:53" end data. string d(a10) t(a8). compute d = char.substr(EndDate,1,10). compute t = char.substr(EndDate,12,8). alter type d (*adate10*) t (time8). compute EndDateNew = d + t. formats EndDateNew(datetime22). * EXECUTE needed before DELETE VARIABLES. execute. delete variables d t. list. OUTPUT: EndDate EndDateNew 09/11/2013 19:19:48 11-SEP-2013 19:19:48 09/11/2013 19:31:23 11-SEP-2013 19:31:23 09/11/2013 19:41:28 11-SEP-2013 19:41:28 09/11/2013 19:59:38 11-SEP-2013 19:59:38 09/11/2013 20:25:18 11-SEP-2013 20:25:18 09/11/2013 22:52:51 11-SEP-2013 22:52:51 09/12/2013 13:44:53 12-SEP-2013 13:44:53 Bob Walker wrote > Hi Gene, > > Yup, tried ALTER TYPE... usually works without any tweaking, but in this > case it reads the month and day in reverse. > > My file comes in as mm/dd/yyyy hh:mm:ss. ALTER TYPE then reads 09/11/2013 > 19:19:48 as 9-Nov-2013 19:19:48 instead of 11-Sep-2013 19:19:48. > > A date such as 09/22/2013 19:19:48 returns a missing value since there is > no month "22". > > So I disaggregated the string and reassembled it, which ultimately works > with ALTER TYPE, but it seems to me that I must be doing something wrong. > > Thanks much, > > Bob > > Surveys & Forecasts, LLC > www.safllc.com<http://www.safllc.com/> > > > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of Maguin, Eugene > Sent: Friday, September 20, 2013 3:35 PM > To: > SPSSX-L@.UGA > Subject: Re: Changing Date Formats > > Bob, did you try and reject due to errors reading the dates as datetime20? > And, if that didn't work, have you tried the Alter type command? > > Gene Maguin > > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of Bob Walker > Sent: Friday, September 20, 2013 3:28 PM > To: > SPSSX-L@.UGA > <mailto: > SPSSX-L@.UGA > > > Subject: Changing Date Formats > > When original dates come in as string, is there an easier way? > > STRING Day Month (A2) Remainder (A60) EndDateNew (A65). > COMPUTE Day = CHAR.SUBSTR(EndDate,4,2). > COMPUTE Month = CHAR.SUBSTR(EndDate,1,2). > COMPUTE Remainder = CHAR.SUBSTR(EndDate,7,57). > COMPUTE EndDateNew = CONCAT(Day, "/", Month, "/", Remainder). > EXECUTE. > ALTER TYPE EndDateNew (DATETIME22.0). > LIST EndDate. > > EndDate > EndDateNew > > 09/11/2013 19:19:48 11-SEP-2013 > 19:19:48 > 09/11/2013 19:31:23 11-SEP-2013 > 19:31:23 > 09/11/2013 19:41:28 11-SEP-2013 > 19:41:28 > 09/11/2013 19:59:38 11-SEP-2013 > 19:59:38 > 09/11/2013 20:25:18 11-SEP-2013 > 20:25:18 > 09/11/2013 22:52:51 11-SEP-2013 > 22:52:51 > 09/12/2013 13:44:53 12-SEP-2013 > 13:44:53 > > Thx, > > Bob Walker > Surveys & Forecasts, LLC > www.safllc.com<http://www.safllc.com/> ----- -- Bruce Weaver [hidden email] http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." NOTE: My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Changing-Date-Formats-tp5722147p5722152.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|
|
In reply to this post by Bruce Weaver
Note that the solution suggested by Dave
Marso assumes that all dates are exactly ten characters. It won't
work with dates like "7/1/01" or even "7/1/2013".
The following should work regardless of the date portion length, assuming there is at least one space between the date and the time: compute #date=number(substr(EndDate, 1, char.index(Enddate, " ")-1), adate10). compute #time=number(substr(EndDate, char.index(Enddate, " ")+1), time8). compute EndDateNew=#date+#time. formats EndDateNew (datetime25). Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Bruce Weaver <[hidden email]> To: [hidden email], Date: 09/20/2013 03:35 PM Subject: Re: Changing Date Formats Sent by: "SPSSX(r) Discussion" <[hidden email]> /Or/ you could do what David suggested! ;-) It's essentially the same as my approach, but cuts it all down to 2 lines. COMPUTE EndDateNew = NUMBER(SUBSTR(EndDate,1,10),ADATE10) + NUMBER(SUBSTR(EndDate,9,8),TIME8). FORMATS EndDateNew (DATETIME20). LIST. OUTPUT: EndDate EndDateNew 09/11/2013 19:19:48 11-SEP-2013 13:19:19 09/11/2013 19:31:23 11-SEP-2013 13:19:31 09/11/2013 19:41:28 11-SEP-2013 13:19:41 09/11/2013 19:59:38 11-SEP-2013 13:19:59 09/11/2013 20:25:18 11-SEP-2013 13:20:25 09/11/2013 22:52:51 11-SEP-2013 13:22:52 09/12/2013 13:44:53 12-SEP-2013 13:13:44 Bruce Weaver wrote > I don't know if this is any "easier" or not, but it is somewhat different > from the method in Bob's original post. It avoids swapping the positions > of mm and dd by using the ADATE10 format for the date part of the original > variable. > > NEW FILE. > DATASET CLOSE all. > > data list list / EndDate(a19). > begin data > "09/11/2013 19:19:48" > "09/11/2013 19:31:23" > "09/11/2013 19:41:28" > "09/11/2013 19:59:38" > "09/11/2013 20:25:18" > "09/11/2013 22:52:51" > "09/12/2013 13:44:53" > end data. > > string d(a10) t(a8). > compute d = char.substr(EndDate,1,10). > compute t = char.substr(EndDate,12,8). > alter type d ( * > adate10 * > ) t (time8). > compute EndDateNew = d + t. > formats EndDateNew(datetime22). > * EXECUTE needed before DELETE VARIABLES. > execute. > delete variables d t. > list. > > OUTPUT: > > EndDate EndDateNew > > 09/11/2013 19:19:48 11-SEP-2013 19:19:48 > 09/11/2013 19:31:23 11-SEP-2013 19:31:23 > 09/11/2013 19:41:28 11-SEP-2013 19:41:28 > 09/11/2013 19:59:38 11-SEP-2013 19:59:38 > 09/11/2013 20:25:18 11-SEP-2013 20:25:18 > 09/11/2013 22:52:51 11-SEP-2013 22:52:51 > 09/12/2013 13:44:53 12-SEP-2013 13:44:53 > > Bob Walker wrote >> Hi Gene, >> >> Yup, tried ALTER TYPE... usually works without any tweaking, but in this >> case it reads the month and day in reverse. >> >> My file comes in as mm/dd/yyyy hh:mm:ss. ALTER TYPE then reads 09/11/2013 >> 19:19:48 as 9-Nov-2013 19:19:48 instead of 11-Sep-2013 19:19:48. >> >> A date such as 09/22/2013 19:19:48 returns a missing value since there is >> no month "22". >> >> So I disaggregated the string and reassembled it, which ultimately works >> with ALTER TYPE, but it seems to me that I must be doing something wrong. >> >> Thanks much, >> >> Bob >> >> Surveys & Forecasts, LLC >> www.safllc.com<http://www.safllc.com/> >> >> >> From: SPSSX(r) Discussion [mailto: >> SPSSX-L@.UGA >> ] On Behalf Of Maguin, Eugene >> Sent: Friday, September 20, 2013 3:35 PM >> To: >> SPSSX-L@.UGA >> Subject: Re: Changing Date Formats >> >> Bob, did you try and reject due to errors reading the dates as >> datetime20? And, if that didn't work, have you tried the Alter type >> command? >> >> Gene Maguin >> >> From: SPSSX(r) Discussion [mailto: >> SPSSX-L@.UGA >> ] On Behalf Of Bob Walker >> Sent: Friday, September 20, 2013 3:28 PM >> To: >> SPSSX-L@.UGA >> <mailto: >> SPSSX-L@.UGA >> > >> Subject: Changing Date Formats >> >> When original dates come in as string, is there an easier way? >> >> STRING Day Month (A2) Remainder (A60) EndDateNew (A65). >> COMPUTE Day = CHAR.SUBSTR(EndDate,4,2). >> COMPUTE Month = CHAR.SUBSTR(EndDate,1,2). >> COMPUTE Remainder = CHAR.SUBSTR(EndDate,7,57). >> COMPUTE EndDateNew = CONCAT(Day, "/", Month, "/", Remainder). >> EXECUTE. >> ALTER TYPE EndDateNew (DATETIME22.0). >> LIST EndDate. >> >> EndDate >> EndDateNew >> >> 09/11/2013 19:19:48 11-SEP-2013 >> 19:19:48 >> 09/11/2013 19:31:23 11-SEP-2013 >> 19:31:23 >> 09/11/2013 19:41:28 11-SEP-2013 >> 19:41:28 >> 09/11/2013 19:59:38 11-SEP-2013 >> 19:59:38 >> 09/11/2013 20:25:18 11-SEP-2013 >> 20:25:18 >> 09/11/2013 22:52:51 11-SEP-2013 >> 22:52:51 >> 09/12/2013 13:44:53 12-SEP-2013 >> 13:44:53 >> >> Thx, >> >> Bob Walker >> Surveys & Forecasts, LLC >> www.safllc.com<http://www.safllc.com/> ----- -- Bruce Weaver [hidden email] http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." NOTE: My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Changing-Date-Formats-tp5722147p5722153.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|
|
Thanks to all for sharing their approaches… this list is the best! In my specific case, the dates are all exactly the same width and format, so Dave’s solution is the most
compact. My larger question was also answered, in that a date using a “string” format such as this must be manually parsed and reassembled to convert it into something usable, unless
it is a string representation of a standard date format. Many thanks, Bob Surveys & Forecasts, LLC From: SPSSX(r) Discussion [mailto:[hidden email]]
On Behalf Of Rick Oliver Note that the solution suggested by Dave Marso assumes that all dates are exactly ten characters. It won't work with dates like "7/1/01" or even "7/1/2013".
|
|
Administrator
|
In reply to this post by Rick Oliver-3
Good catch Rick,
So lets do it like this: -- COMPUTE #parse=CHAR.INDEX(Enddate, " "). COMPUTE EndDateNew = NUMBER(CHAR.SUBSTR(EndDate, 1, #parse-1), ADATE10) + NUMBER(CHAR.SUBSTR(EndDate, #parse +1), TIME8). FORMATS EndDateNew (datetime25).
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
Administrator
|
In reply to this post by Bruce Weaver
The solution in my post below (and David's earlier post) has now been superseded by a more general solution that does not require the dates to all be the same length. However, for the benefit of anyone sifting through the archives in future, note that there was an error in this solution: The extraction of the TIME portion should have started at position 12, not 9. And the final argument for that CHAR.SUBSTR could have been omitted. I.e., the solution could/should have been written as:
COMPUTE EndDateNew = NUMBER(CHAR.SUBSTR(EndDate,1,10),ADATE10) + NUMBER(CHAR.SUBSTR(EndDate,12),TIME8). FORMATS EndDateNew (DATETIME20). HTH.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
| Free forum by Nabble | Edit this page |