Skip to main content

Year Month Extraction Functions

Scenario

The original data is in a format like 2019-09-10, and you want to extract year and month, i.e., 2019-09, how should you handle it?

  1. If it's for direct display in cards, the best solution is to drag the [Month] from the date into the dimension when editing the card.

image.png

  1. If you must use functions and don't have many format requirements, you can directly use the following method:
    But January 2018 will display as 20181
CONCAT(YEAR([Date]),MONTH([Date]))
  1. If you have format requirements, you can use the case when function:
case
when length(month([Date])) =1
then CONCAT(year([Date]),'-0',MONTH([Date]))
else CONCAT(year([Date]),'-',MONTH([Date]))
end
  1. If it's a format like 201909, you can use this writing method:
YEAR([Date])*100+MONTH([Date])

  1. DATE_FORMAT (Date,"YYYY-MM"), the DATE_FORMAT function is not efficient when processing data (related explanation).

  2. CONCAT（substr([Date],1,4),substr([Date],5,3)), but the substr function is mainly used for string processing and doesn't have good advantages on date time fields, not recommended for use.