This article describes different methods for creating date variables in Q.
Variables that are stored with the variable type of Date/Time in the data file will be automatically recognized as dates when the data is imported. For date information stored in different ways (e.g. in Excel files), there are a number of approaches to make them usable as Dates in Q.
Methods
1. Changing the Variable Type
The most straightforward way to create a date variable is to change a variable's Variable Type to Date/Time in the Variables and Questions tab. If it is a Text variable Q will automatically insert a new Date/Time variable underneath.
In situations where Q cannot work out the date correctly, it can be manually set by creating a JavaScript Variable, with an Expression constructed in one of the following ways.
2. Creating an R Variable
- Create > Variables and Questions > Variable(s) > R Variable
- Type in the code flipTime::AsDateTime(datevar), replacing datevar with the name of the variable that contains the dates.
- Press the play button (the blue triangle at the top), enter a Question name at the bottom, and press Add R Variable.
- Change the Variable Type to Date/Time.
3. Encoding using Year, Month and Day
Q contains two special functions for computing dates given knowledge of year (y), month (m), etc:
- Q.EncodeDate(yyyy, mm, dd)
- Q.EncodeDateTime(yyyy, mm, dd, hh, mm, ss, ms)
You can create a JavaScript Variable and use these functions to assign Date values. For an example, see How to Create a Date Variable from a Categorical Variable Using JavaScript.
4. Converting from the YYYYMMDDHHMMSS or YYYY.MM format
The following are all examples of the date and time format YYYYMMDDHHMMSS: 20060929205137, 20060929184343, 20060929161711, 20060929160305, 20060929211356, 20060929190754 and 20060929190948, which are all from the 29th of September 2006. Where the variable v1 is a Text Variable in this format, the following Expression will convert it into a numeric JavaScript Variable:
Math.floor(Q.AsNumeric(v1)/1000000) - 20060000;
Alternatively, to turn this date into year and month (YYYY.MM), use:
Math.floor(Q.AsNumeric(LastResp)/100000000)/100;
5. Reading dates from Text Variables
Where x is the name of your Text Variable:
- Q.AsDate(x) Converts strings into dates. Accepts a variety of formats, but expects dd/mm/yy when in doubt.
- Q.AsDateUS(x) Converts strings into dates. Accepts a variety of formats, but expects mm/dd/yy when in doubt.
- Q.AsDateISO Converts strings into dates. Accepts a variety of formats, but expects yy-mm-dd when in doubt.
Directly computing the date
Q represents dates with the number of milliseconds since 1970, and expressions can be written in JavaScript which converts data to this format. Care should be taken to address date anomalies (e.g., leap years) and differences in time zones.
Other numeric date formats exist:
| Type of Date | Definition | Notes |
|---|---|---|
| Q date | The number of milliseconds since 1 January, 1970 UTC. | Matches the dates used in the JavaScript language. |
| SPSS date | The number of seconds since midnight, October 14, 1582, which was the first day of the Gregorian Calendar. | Date variables in SPSS .sav files will be automatically be converted to Q dates on loading. To manually convert from an SPSS date to a Q (JavaScript) date: (spss_date - 12219379200) * 1000 |
| Excel date | The number of elapsed days since January 1, 1900. For example, Excel represents July 5, 1998, as the number 35981. | Q.ConvertExcelEpochDate() will convert Excel dates to Q dates. |
6. Reading dates from Value Labels
Where the value labels contain the date strings, Q.Label(x) can be used (e.g., Q.AsDate(Q.Label(x))) when creating a JavaScript Variable. Note that if your value labels are ambiguous, you should re-label them first. For example, replace "March 2014" with 1-Mar-2014.
Next
How to Calculate the Duration Between Two Dates
How to Create Filters Using Date Questions
How to Set Time Periods for Date Questions