cancel
Showing results for 
Search instead for 
Did you mean: 

Extract Date Format from Date Column using Extract?

Astroraf
10 - ETL
10 - ETL

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 

1 ACCEPTED SOLUTION

gwolfe
10 - ETL
10 - ETL

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)

View solution in original post

4 REPLIES 4

gwolfe
10 - ETL
10 - ETL

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)

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

gwolfe
10 - ETL
10 - ETL

Glad I could help!

AssafHanina
Sisense Team Member
Sisense Team Member

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.
 
Assaf