Forum Discussion

Astroraf's avatar
Astroraf
Data Pipeline
07-22-2024
Solved

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 

Data Models
  • gwolfe's avatar
    gwolfe
    07-22-2024

    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)

4 Replies

  • gwolfe's avatar
    gwolfe
    ETL
    07-22-2024

    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)

  • AssafHanina's avatar
    AssafHanina
    Sisense Employee
    07-22-2024

    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.