Extracting components from date variables (such as the year or the day of week) can be extremely important in some projects. The key functions that help with this are EXTRACT and TO_CHAR. Which is best depends on what you are trying to do. EXTRACT will always return a number, while TO_CHAR will return a string. Here are some examples. In each example, assume there is a date variable named “mydate” storing the date May 17, 2017.
|
Desired result |
EXTRACT function |
TO_CHAR function |
|
You want a year-month string like “2017-05” |
N/A |
TO_CHAR([mydate], ‘YYYY-MM’) |
|
You want the month number (5), day of month number (17), or year number (2017) |
EXTRACT(day from [mydate]) EXTRACT(month from [mydate]) EXTRACT(year from [mydate]) |
You can use TO_CHAR, but it will return the results as strings, as in: TO_CHAR([mydate], ‘DD’) will return 17 as a string TO_CHAR([mydate], ‘MM’) will return 05 as a string TO_CHAR([mydate], ‘YYYY’) will return 2017 as a string |
|
You want the full name of the month in upper and lower case like “May” or all upper case like ‘MAY’ |
N/A |
TO_CHAR([mydate], ‘Month’) returns ‘May’ TO_CHAR([mydate], ‘MONTH’) would return ‘MAY’ |
|
You want the full day of the week name (as in “Wednesday”) or the day of week number (as in 3, with Monday being the first day of the week). |
EXTRACT(dow from [mydate]) returns the value 3 |
TO_CHAR([mydate], ‘Day’) returns ‘Wednesday’ |
The examples above are common ones, but others include quarter and week (which returns the week number in the year).
The EXTRACT uses “datepart” references in the function before the “from” keyword. These are documented in the DATEADD function: https://support.lityxiq.com/206665-Date--Time-Functions.
The TO_CHAR and TO_DATE functions use format strings as in the examples above. The full set of possible strings are documented here: https://support.lityxiq.com/413446-DateTime-Formatting-Strings.