Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Write a SQL filter to extract the month value from a time column in a data source

This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release. 

Question

How can I write a SQL filter to extract the month value from a time column in my data source?

Answer

When extracting the month from a time column, it will take a single digit for January-September rather than a 2 digit month which would be used for October-December. This means it will pull the value of  "1" for January. Because the CAST(expr AS INTEGER) functionality for casting to a date format requires two digits, January- September will not work without a 0 at the front the CAST(expr AS INTEGER). You can use conditional formatting to add the 0 at the beginning if the length of month is 1 digit.

For example:

CASE WHEN LENGTH(CAST(EXTRACT(MONTH FROM "Adaptive Month") AS NVARCHAR)) = 1 THEN ('0' || CAST(EXTRACT(MONTH FROM "Adaptive Month") AS NVARCHAR)) ELSE CAST(EXTRACT(MONTH FROM "Adaptive Month") AS NVARCHAR) END

This may need to be done for any dates that equal 1 digit.