How to plus 150 days from a previouse extracted day?
Locked
 
|
Hello,
I use ' WHERE(EMPLOYMENT.START_DATE < GETDATE() +150)' to extract employment data from our staging database. Now I would like to repeat a list extracted on 10/20/2010. How can I change the SPSS? I can definite the day of 10/20/2010 as below. DEFINE !sqlDate() '''10/20/2010'''!ENDDEFINE /*Data Extract's Date */. But I don't know how to plus 150 days from '!sqlDate' instead of 'GETDATE () +150.' Any advices are appreciated! ===================== 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 |
Re: How to plus 150 days from a previouse extracted day?
|
|
I recently did something similar where I had to add 180 days to a date, and I modified the code found here: http://www.spsstools.net/Syntax/DatesTime/DatePlus3Months.txt
-----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Ilybbb Sent: Friday, February 25, 2011 5:24 PM To: [hidden email] Subject: How to plus 150 days from a previouse extracted day? Hello, I use ' WHERE(EMPLOYMENT.START_DATE < GETDATE() +150)' to extract employment data from our staging database. Now I would like to repeat a list extracted on 10/20/2010. How can I change the SPSS? I can definite the day of 10/20/2010 as below. DEFINE !sqlDate() '''10/20/2010'''!ENDDEFINE /*Data Extract's Date */. But I don't know how to plus 150 days from '!sqlDate' instead of 'GETDATE () +150.' Any advices are appreciated! ===================== 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 |
Re: How to plus 150 days from a previouse extracted day?
Administrator
|
Using the data from that link:
DATA LIST /date1(DATE12). BEGIN DATA. 30-DEC-1999 31-JAN-2000 29-MAR-1999 30-MAR-1999 31-MAR-1999 1-APR-1999 5-FEB-1999 28-NOV-1999 29-NOV-1999 30-NOV-1999 28-NOV-2000 29-NOV-2000 30-NOV-2000 15-DEC-1998 1-DEC-2000 1-DEC-1999 END DATA. numeric date2(date12). * Date2 = Date1 + 150 days. * But date variables are stored as seconds * since 14-Oct-1582 00:00:00; so we need * to the number of seconds in 150 days. compute date2 = date1 + 150*24*60*60. * Use DATEDIFF to check. compute diff = datediff(date2,date1,"days"). format diff (f5.0). list.
--
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/). |
Re: How to plus 150 days from a previouse extracted day?
|
|
In reply to this post by Hart, Kimberly (hartkb)
It's relatively straightforward once the
data are in in the SPSS Statistics:
select if start_date<datesum(date.mdy(10,20,2010), 150, 'days'). If you want to select the data in the database BEFORE reading it into SPSS Statistics, you can specify dates in the WHERE clause according to these syntax rules (note the use of curly brackets, not parentheses): If the SQL contains WHERE clauses with expressions for case selection, dates and times in expressions need to be specified in a special manner (including the curly braces shown in the examples): • Date literals should be specified using the general form {d 'yyyy-mm-dd'}. • Time literals should be specified using the general form {t 'hh:mm:ss'}. • Date/time literals (timestamps) should be specified using the general form {ts 'yyyy-mm-dd hh:mm:ss'}. • The entire date and/or time value must be enclosed in single quotes. Years must be expressed in four-digit form, and dates and times must contain two digits for each portion of the value. For example January 1, 2005, 1:05 AM would be expressed as: {ts '2005-01-01 01:05:00'}
I recently did something similar where I had to add 180 days to a date, and I modified the code found here: http://www.spsstools.net/Syntax/DatesTime/DatePlus3Months.txt -----Original Message----- From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Ilybbb Sent: Friday, February 25, 2011 5:24 PM To: [hidden email] Subject: How to plus 150 days from a previouse extracted day? Hello, I use ' WHERE(EMPLOYMENT.START_DATE < GETDATE() +150)' to extract employment data from our staging database. Now I would like to repeat a list extracted on 10/20/2010. How can I change the SPSS? I can definite the day of 10/20/2010 as below. DEFINE !sqlDate() '''10/20/2010'''!ENDDEFINE /*Data Extract's Date */. But I don't know how to plus 150 days from '!sqlDate' instead of 'GETDATE () +150.' Any advices are appreciated! ===================== 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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |