Extract Date Format from Date Column using Extract?

[Astroraf]

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 

[gwolfe · Accepted solution]

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)

[Astroraf]

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

[gwolfe]

Glad I could help!

[AssafHanina]

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.