Creating Date Variables

From Q
Jump to navigation Jump to search

Variables that are stored with the Type of Date in the SPSS 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.

Changing the Variable Type

The most straightforward way to create a Date Variable is to change a variable's Variable Type to Date in the Variables and Questions tab. If it is a Text Variable Q will automatically insert a new Date 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.

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.

Encoding using Year, Month and Day

Q contains two special functions for computing dates given knowledge of year (y), month (m), etc:

Q.EncodeDate(y, m, d)

Q.EncodeDateTime(y, m, d, hh, mm, ss, ms)

You can create a JavaScript Variable and use these functions to assign Date values. For an example, see Creating a Date Variable from Categories.

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:


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.

Reading dates from a 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.

See also

Using Date Questions With Irregular Time Periods.