Extract Date Format from Date Column using Extract?

I am trying to extract the DAY, MONTH, YEAR from a date column from my date column to create a dim_date table in my model.

Currently my code goes as follows:

SELECT

DISTINCT birthdate AS Date,

YEAR(birthdate) AS Year,

MONTH(birthdate) AS Month,

DAY(birthdate) AS Day

FROM FACT_table

UNION

SELECT

DISTINCT hire_date AS Date,

YEAR(hire_date) AS Year,

MONTH(hire_date) AS Month,

DAY(hire_date) AS Day

FROM FACT_table

I get this error: Function 'YEAR(DateTime)' is not declared. What is the right syntax to extract the various DATE formats from a column from a fact table?

DRay

Rafael Ferreira

Posted 2 years ago·Last reply 2 years ago

4 comments

Assaf Hanina

·2 years ago

Hey Astroraf ,

Alongside the solution suggested by gwolfe, please consider creating a Dim_Date table in the database. This table can be generated using a static table (e.g., CSV, Python script) and should include additional information about each date.

Main Benefits:

Comprehensive Columns: Include related columns such as: DateNum, Date, Year, Month, Day Name, Month Name, Quarter, etc.
Efficient Relations: Create relations on DateNum (integer) rather than Date (DateTime).
Sequential Dates: Ensure a sequence of dates that is not dependent on creating it from fact tables.
Future Dates: Include future dates for forecasting purposes if needed
Multiple Relations: Easily relate to each fact table without the need to update the union.

Garrett Wolfe

·2 years ago

Glad I could help!

Garrett Wolfe

·2 years ago

Hey Astroraf are you doing this in a custom table (green node in the elasticube)? If so the syntax is GETYEAR(yourdate),

GETMONTH(yourdate),

GETDAY(yourdate)

Rafael Ferreira

OP2 years ago

Thank you for this gwolfe, I did not see documentation on this.