SPSSX Discussion

String date conversion

Classic

List

Threaded

16 messages Options

Jignesh Sutar

String date conversion

This post was updated on .

Can a date stored as a string in the format YYYYMMDD be converted to a Date format variable directly using ALTER TYPE?

DATA LIST LIST /Date.
BEGIN DATA.
"20150401"
END DATA.

ALTER TYPE Date (SDATE10) /* does not work */ .

I'm aware of the indirect alternative:

compute #Day=char.substr(string(date,f8.0),7,2).
compute #Month=char.substr(string(date,f8.0),5,2).
compute #Year=char.substr(string(date,f8.0),1,4).

compute DateFormat=DATE.DMY(#Day,#Month,#Year)

Bruce Weaver

Re: String date conversion

Administrator

Part of your problem, I think, is that your small sample dataset has Date as a numeric variable with two decimals (the default). If you read Date in as string, things might work better. I believe you also have to insert slashes before using ALTER TYPE. Here is a variation on your example to demonstrate.

NEW FILE.
DATASET CLOSE all.
DATA LIST LIST / Date1(A8).
BEGIN DATA.
"20150401"
END DATA.

STRING Date2 (A10).
COMPUTE Date2 = CONCAT(CHAR.SUBSTR(Date1,1,4),"/",CHAR.SUBSTR(Date1,5,2),"/",CHAR.SUBSTR(Date1,7,2)).
ALTER TYPE Date1 Date2 (SDATE10).
LIST.

OUTPUT:

Date1 Date2

. 2015/04/01

ALTER TYPE worked on Date2, which had the inserted slashes, but not on Date1 (with no inserted slashes).

HTH.

Jignesh Sutar wrote

Can a date stored as a string in the format YYYYMMDD be converted to a Date format variable directly using ALTER TYPE?

DATA LIST LIST /Date.
BEGIN DATA.
"20150401"
END DATA.

ALTER TYPE Date (SDATE10) /* does not work */ .

I'm aware of the indirect alternative:

compute #Day=char.substr(string(date,f8.0),7,2).
compute #Month=char.substr(string(date,f8.0),5,2).
compute #Year=char.substr(string(date,f8.0),1,4).

compute DateFormat=DATE.DMY(#Day,#Month,#Year)

--
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/).

Jon K Peck

Re: String date conversion

In reply to this post by Jignesh Sutar

It could, except that there isn't a built-in format that matches that date format.

data list list/d(A10).
begin data
2000/01/21
2015/01/01
end data.
dataset name dates.
list.
alter type d (sdate10).

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Jignesh Sutar <[hidden email]>
To: [hidden email]
Date: 04/01/2015 08:11 AM
Subject: [SPSSX-L] String date conversion
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Can a date stored as a string in the format YYYYMMDD being converted to a Date format variable *directly *using ALTER TYPE? CSD RESET. DATA LIST LIST /Date. BEGIN DATA. "20150401" END DATA. ALTER TYPE Date (SDATE10) /* does not work */ . I'm aware of the indirect alternative: compute #Day=char.substr(string(date,f8.0),7,2). compute #Month=char.substr(string(date,f8.0),5,2). compute #Year=char.substr(string(date,f8.0),1,4). compute DateFormat=DATE.DMY(#Day,#Month,#Year) -- View this message in context:http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107.htmlSent 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

David Marso

Re: String date conversion

Administrator

In reply to this post by Jignesh Sutar

You have discovered the answer is No. ;-)
Another alternative:

DATA LIST LIST /Date (A8).
BEGIN DATA.
20150401
END DATA.
ALTER TYPE Date (A10) .
COMPUTE date=CONCAT(CHAR.SUBSTR(Date,1,4),"/",CHAR.SUBSTR(Date,5,2),"/",CHAR.SUBSTR(Date,7,2)).
ALTER TYPE date (SDATE).
LIST.

Jignesh Sutar wrote

Can a date stored as a string in the format YYYYMMDD be converted to a Date format variable directly using ALTER TYPE?

DATA LIST LIST /Date.
BEGIN DATA.
"20150401"
END DATA.

ALTER TYPE Date (SDATE10) /* does not work */ .

I'm aware of the indirect alternative:

compute #Day=char.substr(string(date,f8.0),7,2).
compute #Month=char.substr(string(date,f8.0),5,2).
compute #Year=char.substr(string(date,f8.0),1,4).

compute DateFormat=DATE.DMY(#Day,#Month,#Year)

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?"

Jignesh Sutar

Re: String date conversion

In reply to this post by Bruce Weaver

Sorry, I didn't do a good job of setting up the example data. The intention was to define Date variable as a string (not to allow it to come through as the default F8.2 by not specifying a format).

Fundamentally your solution is the same as I had posted. I just wanted if there was a DATE format (because there are many variations) that I could have used with ALTER TYPE directly. Perhaps there isn't, I simply need to use the work around, which is easy enough.

For example, sometime you have to use multiple ALTER TYPES to get to the desired DATE format:

DATA LIST LIST /Date (A10).

BEGIN DATA.

"01/04/2015"

END DATA.

ALTER TYPE Date (ADATE10) .

ALTER TYPE Date (SDATE10) .

Jumping straight to SDATE doesn't work, you have to first match to ADATE and then SDATE so was wondering if there was something equivalent for this particular case use.

On 1 April 2015 at 16:12, Bruce Weaver <[hidden email]> wrote:

Part of your problem, I think, is that your small sample dataset has Date as
a numeric variable with two decimals (the default). If you read Date in as
string, things might work better. I believe you also have to insert slashes
before using ALTER TYPE. Here is a variation on your example to
demonstrate.

NEW FILE.
DATASET CLOSE all.
DATA LIST LIST / Date1(A8).
BEGIN DATA.
"20150401"
END DATA.

STRING Date2 (A10).
COMPUTE Date2 =
CONCAT(CHAR.SUBSTR(Date1,1,4),"/",CHAR.SUBSTR(Date1,5,2),"/",CHAR.SUBSTR(Date1,7,2)).
ALTER TYPE Date1 Date2 (SDATE10).
LIST.

OUTPUT:

Date1 Date2

. 2015/04/01

ALTER TYPE worked on Date2, which had the inserted slashes, but not on Date1
(with no inserted slashes).

HTH.

Jignesh Sutar wrote
> Can a date stored as a string in the format YYYYMMDD be converted to a
> Date format variable
*
> directly
*
> using ALTER TYPE?
>
>
> DATA LIST LIST /Date.
> BEGIN DATA.
> "20150401"
> END DATA.
>
> ALTER TYPE Date (SDATE10) /* does not work */ .
>
>
>
> I'm aware of the indirect alternative:
>
> compute #Day=char.substr(string(date,f8.0),7,2).
> compute #Month=char.substr(string(date,f8.0),5,2).
> compute #Year=char.substr(string(date,f8.0),1,4).
>
> compute DateFormat=DATE.DMY(#Day,#Month,#Year)

-----
--
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/String-date-conversion-tp5729107p5729108.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

Jignesh Sutar

Re: String date conversion

In reply to this post by Jon K Peck

Could/should there be? As date of this format I would assume is quite common?

On 1 April 2015 at 16:20, Jon K Peck <[hidden email]> wrote:

It could, except that there isn't a built-in format that matches that date format.

data list list/d(A10).
begin data
2000/01/21
2015/01/01
end data.
dataset name dates.
list.
alter type d (sdate10).

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Jignesh Sutar <[hidden email]>
To: [hidden email]
Date: 04/01/2015 08:11 AM
Subject: [SPSSX-L] String date conversion
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Can a date stored as a string in the format YYYYMMDD being converted to a Date format variable *directly *using ALTER TYPE? CSD RESET. DATA LIST LIST /Date. BEGIN DATA. "20150401" END DATA. ALTER TYPE Date (SDATE10) /* does not work */ . I'm aware of the indirect alternative: compute #Day=char.substr(string(date,f8.0),7,2). compute #Month=char.substr(string(date,f8.0),5,2). compute #Year=char.substr(string(date,f8.0),1,4). compute DateFormat=DATE.DMY(#Day,#Month,#Year) -- View this message in context:http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107.htmlSent 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

Rick Oliver-3

Re: String date conversion

In reply to this post by Jignesh Sutar

If there are no delimiters between month, day, and, year values, then there is no simple one-step conversion solution.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]

From: Jignesh Sutar <[hidden email]>
To: [hidden email]
Date: 04/01/2015 10:33 AM
Subject: Re: String date conversion
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Sorry, I didn't do a good job of setting up the example data. The intention was to define Date variable as a string (not to allow it to come through as the default F8.2 by not specifying a format).

Fundamentally your solution is the same as I had posted. I just wanted if there was a DATE format (because there are many variations) that I could have used with ALTER TYPE directly. Perhaps there isn't, I simply need to use the work around, which is easy enough.

For example, sometime you have to use multiple ALTER TYPES to get to the desired DATE format:

DATA LIST LIST /Date (A10).
BEGIN DATA.
"01/04/2015"
END DATA.

ALTER TYPE Date (ADATE10) .
ALTER TYPE Date (SDATE10) .

Jumping straight to SDATE doesn't work, you have to first match to ADATE and then SDATE so was wondering if there was something equivalent for this particular case use.

On 1 April 2015 at 16:12, Bruce Weaver <bruce.weaver@...> wrote:
Part of your problem, I think, is that your small sample dataset has Date as
a numeric variable with two decimals (the default). If you read Date in as
string, things might work better. I believe you also have to insert slashes
before using ALTER TYPE. Here is a variation on your example to
demonstrate.

NEW FILE.
DATASET CLOSE all.
DATA LIST LIST / Date1(A8).
BEGIN DATA.
"20150401"
END DATA.

STRING Date2 (A10).
COMPUTE Date2 =
CONCAT(CHAR.SUBSTR(Date1,1,4),"/",CHAR.SUBSTR(Date1,5,2),"/",CHAR.SUBSTR(Date1,7,2)).
ALTER TYPE Date1 Date2 (SDATE10).
LIST.

OUTPUT:

Date1 Date2

. 2015/04/01

ALTER TYPE worked on Date2, which had the inserted slashes, but not on Date1
(with no inserted slashes).

HTH.

Jignesh Sutar wrote
> Can a date stored as a string in the format YYYYMMDD be converted to a
> Date format variable
*
> directly
*
> using ALTER TYPE?
>
>
> DATA LIST LIST /Date.
> BEGIN DATA.
> "20150401"
> END DATA.
>
> ALTER TYPE Date (SDATE10) /* does not work */ .
>
>
>
> I'm aware of the indirect alternative:
>
> compute #Day=char.substr(string(date,f8.0),7,2).
> compute #Month=char.substr(string(date,f8.0),5,2).
> compute #Year=char.substr(string(date,f8.0),1,4).
>
> compute DateFormat=DATE.DMY(#Day,#Month,#Year)

-----
--
Bruce Weaver
bweaver@...
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/String-date-conversion-tp5729107p5729108.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (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 LISTSERV@... (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

David Marso

Re: String date conversion

Administrator

You could also use FORMAT var (SDATE) on the ADATE.

ALTER TYPE is more expensive run time hit.

On Wed, Apr 1, 2015 at 11:42 AM, Rick Oliver [via SPSSX Discussion] <[hidden email]> wrote:

If there are no delimiters between month, day, and, year values, then there is no simple one-step conversion solution.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]

From: Jignesh Sutar <[hidden email]>
To: [hidden email]
Date: 04/01/2015 10:33 AM
Subject: Re: String date conversion
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Sorry, I didn't do a good job of setting up the example data. The intention was to define Date variable as a string (not to allow it to come through as the default F8.2 by not specifying a format).

Fundamentally your solution is the same as I had posted. I just wanted if there was a DATE format (because there are many variations) that I could have used with ALTER TYPE directly. Perhaps there isn't, I simply need to use the work around, which is easy enough.

For example, sometime you have to use multiple ALTER TYPES to get to the desired DATE format:

DATA LIST LIST /Date (A10).
BEGIN DATA.
"01/04/2015"
END DATA.

ALTER TYPE Date (ADATE10) .
ALTER TYPE Date (SDATE10) .

Jumping straight to SDATE doesn't work, you have to first match to ADATE and then SDATE so was wondering if there was something equivalent for this particular case use.

On 1 April 2015 at 16:12, Bruce Weaver <[hidden email]> wrote:
Part of your problem, I think, is that your small sample dataset has Date as
a numeric variable with two decimals (the default). If you read Date in as
string, things might work better. I believe you also have to insert slashes
before using ALTER TYPE. Here is a variation on your example to
demonstrate.

NEW FILE.
DATASET CLOSE all.
DATA LIST LIST / Date1(A8).
BEGIN DATA.
"20150401"
END DATA.

STRING Date2 (A10).
COMPUTE Date2 =
CONCAT(CHAR.SUBSTR(Date1,1,4),"/",CHAR.SUBSTR(Date1,5,2),"/",CHAR.SUBSTR(Date1,7,2)).
ALTER TYPE Date1 Date2 (SDATE10).
LIST.

OUTPUT:

Date1 Date2

. 2015/04/01

ALTER TYPE worked on Date2, which had the inserted slashes, but not on Date1
(with no inserted slashes).

HTH.

Jignesh Sutar wrote
> Can a date stored as a string in the format YYYYMMDD be converted to a
> Date format variable
*
> directly
*
> using ALTER TYPE?
>
>
> DATA LIST LIST /Date.
> BEGIN DATA.
> "20150401"
> END DATA.
>
> ALTER TYPE Date (SDATE10) /* does not work */ .
>
>
>
> I'm aware of the indirect alternative:
>
> compute #Day=char.substr(string(date,f8.0),7,2).
> compute #Month=char.substr(string(date,f8.0),5,2).
> compute #Year=char.substr(string(date,f8.0),1,4).
>
> compute DateFormat=DATE.DMY(#Day,#Month,#Year)

-----
--
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/String-date-conversion-tp5729107p5729108.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
===================== 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

If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107p5729113.html

To unsubscribe from String date conversion, click here.
NAML

MLIves

Re: String date conversion

In reply to this post by Jignesh Sutar

You could do it all in one command with a format of either ADATE10 or SDATE10.

compute DateFormat=DATE.DMY(Number(char.substr(date,7,2),F2.0), Number(char.substr(date,5,2),F2.0), Number(char.substr(date,1,4),F4.0)).
format DateFormat (sdate10).

Melissa
-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jignesh Sutar
Sent: Wednesday, April 01, 2015 10:11 AM
To: [hidden email]
Subject: [SPSSX-L] String date conversion

Can a date stored as a string in the format YYYYMMDD being converted to a Date format variable *directly *using ALTER TYPE?

CSD RESET.
DATA LIST LIST /Date.
BEGIN DATA.
"20150401"
END DATA.

ALTER TYPE Date (SDATE10) /* does not work */ .

I'm aware of the indirect alternative:

compute #Day=char.substr(string(date,f8.0),7,2).
compute #Month=char.substr(string(date,f8.0),5,2).
compute #Year=char.substr(string(date,f8.0),1,4).

compute DateFormat=DATE.DMY(#Day,#Month,#Year)

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107.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

This correspondence contains proprietary information some or all of which may be legally privileged; it is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this correspondence and completely dispose of the correspondence immediately. Please notify the sender if you have received this email in error. NOTE: Messages to or from the State of Connecticut domain may be subject to the Freedom of Information statutes and regulations.

=====================
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

Jignesh Sutar

Re: String date conversion

I receive date variables in all sort of formats so I've now built an extension command in which I am collating different date formats that can be converted to a known date format. With the additional benefit of the conversion preserving the original date variable name and position in the file.

So a couple of input formats I have accounted for at the moment are:

31dec2015 (strings)

20151231 (numerics)

Which both get converted to my preferred format SDATE10 (from which they can be converted to any other recognized format).

If this is of interest to anyone, I can share.

On 1 April 2015 at 18:47, Ives, Melissa L <[hidden email]> wrote:

You could do it all in one command with a format of either ADATE10 or SDATE10.

compute DateFormat=DATE.DMY(Number(char.substr(date,7,2),F2.0), Number(char.substr(date,5,2),F2.0), Number(char.substr(date,1,4),F4.0)).
format DateFormat (sdate10).

Melissa
-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jignesh Sutar
Sent: Wednesday, April 01, 2015 10:11 AM
To: [hidden email]
Subject: [SPSSX-L] String date conversion

Can a date stored as a string in the format YYYYMMDD being converted to a Date format variable *directly *using ALTER TYPE?

CSD RESET.
DATA LIST LIST /Date.
BEGIN DATA.
"20150401"
END DATA.

ALTER TYPE Date (SDATE10) /* does not work */ .

I'm aware of the indirect alternative:

compute #Day=char.substr(string(date,f8.0),7,2).
compute #Month=char.substr(string(date,f8.0),5,2).
compute #Year=char.substr(string(date,f8.0),1,4).

compute DateFormat=DATE.DMY(#Day,#Month,#Year)

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107.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

This correspondence contains proprietary information some or all of which may be legally privileged; it is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this correspondence and completely dispose of the correspondence immediately. Please notify the sender if you have received this email in error. NOTE: Messages to or from the State of Connecticut domain may be subject to the Freedom of Information statutes and regulations.

David Marso

Re: String date conversion

Administrator

My solution does it in place.

On Wed, Apr 1, 2015 at 3:03 PM, Jignesh Sutar [via SPSSX Discussion] <[hidden email]> wrote:

Thanks! I was hoping to avoid the whole NUMBER / CHAR.SUBSTR rigmarole even though it is perhaps one/two commands at most, you also have to create a date new variable with new name (rather than being able to overwrite as is the case with ALTER TYPE). And the new variable ends up being at the end of the file and looses its natural position in the file. If you try to do all this then it because more than just a couple of commands.

I receive date variables in all sort of formats so I've now built an extension command in which I am collating different date formats that can be converted to a known date format. With the additional benefit of the conversion preserving the original date variable name and position in the file.

So a couple of input formats I have accounted for at the moment are:
31dec2015 (strings)
20151231 (numerics)

Which both get converted to my preferred format SDATE10 (from which they can be converted to any other recognized format).

If this is of interest to anyone, I can share.

On 1 April 2015 at 18:47, Ives, Melissa L <[hidden email]> wrote:
You could do it all in one command with a format of either ADATE10 or SDATE10.

compute DateFormat=DATE.DMY(Number(char.substr(date,7,2),F2.0), Number(char.substr(date,5,2),F2.0), Number(char.substr(date,1,4),F4.0)).
format DateFormat (sdate10).

Melissa
-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jignesh Sutar
Sent: Wednesday, April 01, 2015 10:11 AM
To: [hidden email]
Subject: [SPSSX-L] String date conversion

Can a date stored as a string in the format YYYYMMDD being converted to a Date format variable *directly *using ALTER TYPE?

CSD RESET.
DATA LIST LIST /Date.
BEGIN DATA.
"20150401"
END DATA.

ALTER TYPE Date (SDATE10) /* does not work */ .

I'm aware of the indirect alternative:

compute #Day=char.substr(string(date,f8.0),7,2).
compute #Month=char.substr(string(date,f8.0),5,2).
compute #Year=char.substr(string(date,f8.0),1,4).

compute DateFormat=DATE.DMY(#Day,#Month,#Year)

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107.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

This correspondence contains proprietary information some or all of which may be legally privileged; it is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this correspondence and completely dispose of the correspondence immediately. Please notify the sender if you have received this email in error. NOTE: Messages to or from the State of Connecticut domain may be subject to the Freedom of Information statutes and regulations.

===================== 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

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107p5729118.html

To unsubscribe from String date conversion, click here.
NAML

Jon K Peck

Re: String date conversion

In reply to this post by Jignesh Sutar

Bear in mind that the strtodatetime function in the extendedTransforms.py module can handle pretty much any date format. You can use it with the SPSSINC TRANS extension command.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Jignesh Sutar <[hidden email]>
To: [hidden email]
Date: 04/01/2015 01:03 PM
Subject: Re: [SPSSX-L] String date conversion
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Thanks! I was hoping to avoid the whole NUMBER / CHAR.SUBSTR rigmarole even though it is perhaps one/two commands at most, you also have to create a date new variable with new name (rather than being able to overwrite as is the case with ALTER TYPE). And the new variable ends up being at the end of the file and looses its natural position in the file. If you try to do all this then it because more than just a couple of commands.

I receive date variables in all sort of formats so I've now built an extension command in which I am collating different date formats that can be converted to a known date format. With the additional benefit of the conversion preserving the original date variable name and position in the file.

So a couple of input formats I have accounted for at the moment are:
31dec2015 (strings)
20151231 (numerics)

Which both get converted to my preferred format SDATE10 (from which they can be converted to any other recognized format).

If this is of interest to anyone, I can share.

On 1 April 2015 at 18:47, Ives, Melissa L <Melissa.Ives@...> wrote:
You could do it all in one command with a format of either ADATE10 or SDATE10.

compute DateFormat=DATE.DMY(Number(char.substr(date,7,2),F2.0), Number(char.substr(date,5,2),F2.0), Number(char.substr(date,1,4),F4.0)).
format DateFormat (sdate10).

Melissa
-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jignesh Sutar
Sent: Wednesday, April 01, 2015 10:11 AM
To: [hidden email]
Subject: [SPSSX-L] String date conversion

Can a date stored as a string in the format YYYYMMDD being converted to a Date format variable *directly *using ALTER TYPE?

CSD RESET.
DATA LIST LIST /Date.
BEGIN DATA.
"20150401"
END DATA.

ALTER TYPE Date (SDATE10) /* does not work */ .

I'm aware of the indirect alternative:

compute #Day=char.substr(string(date,f8.0),7,2).
compute #Month=char.substr(string(date,f8.0),5,2).
compute #Year=char.substr(string(date,f8.0),1,4).

compute DateFormat=DATE.DMY(#Day,#Month,#Year)

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to LISTSERV@... (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

This correspondence contains proprietary information some or all of which may be legally privileged; it is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this correspondence and completely dispose of the correspondence immediately. Please notify the sender if you have received this email in error. NOTE: Messages to or from the State of Connecticut domain may be subject to the Freedom of Information statutes and regulations.

===================== To manage your subscription to SPSSX-L, send a message to LISTSERV@... (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

PRogman

Re: String date conversion

I am not sure about the SPSS TRANS command, but I do think that the format YYYY-MM-DD (ISO 8601) should be supported in native SPSS. YYYYMMDD is a common abbreviated format.
/PR

David Marso

Re: String date conversion

Administrator

DATA LIST LIST /Date (SDATE).
BEGIN DATA.
2015-04-01
END DATA.
LIST.

On Thu, Apr 2, 2015 at 6:49 AM, PRogman [via SPSSX Discussion] <[hidden email]> wrote:

I am not sure about the SPSS TRANS command, but I do think that the format YYYY-MM-DD (ISO 8601) should be supported in native SPSS. YYYYMMDD is a common abbreviated format.
/PR

If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/String-date-conversion-tp5729107p5729130.html

To unsubscribe from String date conversion, click here.
NAML

PRogman

Re: String date conversion

Well, well... I seem to stand corrected.
I have not (yet) seen the YYYY-MM-DD format accepted in the editor, the closest thing is YYYY/MM/DD.
In the FM under 'Date and Time Formats' (v22, page 55) the ISO format is not listed.

The page on the IBM site does not include ISO format (http://www-01.ibm.com/support/docview.wss?uid=swg21476991)

I would prefer if all valid input formats (reading and data entry) also were available display formats.

/PR

Richard Ristow

Re: String date conversion

In reply to this post by Jignesh Sutar

At 11:22 AM 4/1/2015, Jignesh Sutar wrote:

>Sometime you have to use multiple ALTER TYPES to get to the desired
>DATE format:
>
>DATA LIST LIST /Date (A10).
>BEGIN DATA.
>"01/04/2015"
>END DATA.
>
>ALTER TYPE Date (ADATE10).
>ALTER TYPE Date (SDATE10).

It's worth noting that the second ALTER TYPE is not necessary,
although I'm sure that it will work.

The first ALTER TYPE doesn't convert the string to an ADATE10 date;
it converts it to an SPSS date, with display format set to ADATE10.
Changing the display format does not change the internal value, and
does not require changing the type:

FORMATS Date (SDATE10).

SPSS dates are not in format SDATE10, nor ADATE10, nor any standard
readable format. SPSS uses an "epoch and offset"
representation: the internal form is the number of seconds since the
midnight that began 14 Oct. 1582. The smallest number recognized as a
date is 86,400, representing the beginning of 15 Oct. 1582; numbers
for dates near the present are correspondingly large. Here's an
illustration, showing that the same value can be displayed as either
a date or a number -- notice that DateVal is numerically equal to Date:

DATA LIST LIST/
Date (ADATE10).
BEGIN DATA
10/15/1582
04/02/2015
END DATA.

FORMATS Date (SDATE10).
NUMERIC DateVal (COMMA15).

COMPUTE DateVal=Date.

LIST.
|-----------------------------|---------------------------|
|Output Created |02-APR-2015 16:01:25 |
|-----------------------------|---------------------------|
Date DateVal
1582/10/15 86,400
2015/04/02 13,647,312,000

Number of cases read: 2 Number of cases
listed: 2============================
APPENDIX: Test data and code
============================
NEW FILE.
DATA LIST LIST/
Date (ADATE10).
BEGIN DATA
10/15/1582
04/02/2015
END DATA.

FORMATS Date (SDATE10).
NUMERIC DateVal (COMMA15).

COMPUTE DateVal=Date.

LIST.

=====================
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