Open topic with navigation

Database reference guide

 HOME

DATE Columns

YEAR(<column>) - returns the year of each record

MONTH(<column>) - returns the month of each record ( 1-12)

DAY(<column>) - returns the day of each record ( 1-31)

WEEKDAY(<column>) - returns the day of week for each record ( 1-7 1 is Monday)

QUARTER(<column>) - returns the quarter of the year for each record (1-4)

ALLMONTHS(<column>) - returns the year and month for each record in the format CCYYMM (e.g. 199805 would be returned for May 1998)

ALLQUARTERS(<column>) - returns the year and quarter for each record in the format CCYYQQ (e.g. 199802 would be returned for second quarter 1998)

AGE(<column>, “DATE”) - returns the age of each record at the specified date. Both parameters must be of the same data type (Date or DateTime).

AGE(<column>, _Today) - To calculate an age dynamically, during a load process for example, you could use the _Today variable (which is a Date data type).

DAYSTO(<column>, “DATE”) - returns the number of days between each record and the specified date. Both parameters must be of the same data type (Date or DateTime).

DAYAT([Date_Field], <integer>) - adds or subtracts (with - sign) <integer> number of days to a date field

The DATE parameters in these functions represent fixed dates, enclosed in quotes and entered into the system administrator. For example: in the UK, to find the number of people in a table who will be 21 on or before Christmas Day 2001, use:

SELECT Count(*) - FROM Customer

WHERE AGE(DOB,”25122001”) = 21;

Given that DOB is a column containing each customer’s date of birth.

Incidentally, we can find the number of people who will be 21 on Christmas Day 2001 with the statement:

SELECT count(*)

FROM Customer

WHERE AGE(DOB,”25122001”) = 21 AND MONTH(DOB) = 12 AND DAY(DOB) = 25;

To add 4 days to a date of birth use the DAYAT function with the statement:

DAYAT (DOB),4)

Or to take 4 days away from a date of birth use the statement:

DAYAT (DOB),-4)

To ensure that the two parameters in the AGE and DAYSTO functions are the same data type, the DATE or DATETIME functions can be used to convert the dates to the same format:

AGE(DATE([datetimecreated]), _TODAY) or AGE([datetimecreated], DATETIME(_TODAY))

  Online & Instructor-Led Courses | Training Videos | Webinar Recordings
© Alterian. All Rights Reserved. | Privacy Policy | Legal Notice