SQL Date Case Practice Cases
This article only applies to Spark, different direct connection databases DateFormatter are not universal. For details, see: [Spark Date Functions and Applications](1-Spark Functions/1-Spark Date Functions and Applications.md)
Uppercase Y and Lowercase y
Case One: Natural Year and Week Year
Today is December 28, 2020, using time macro to get year and month: {{{yesterday %YMMdd}}}, returns 20211228

Cause
In SQL, y is Year (natural year), Y represents Week year (week year);
Week year means the year to which the current day's week belongs. A week starts from Sunday and ends on Saturday. As long as this week crosses the year, this week is counted as next year.
【Example】: 2020-12-28 This week happens to cross the year, and Week year is calculated from last Sunday, so yesterday (December 27, 2020, Sunday) happens to be the first day of the cross-year week, belonging to the first week of 2021, so the above situation occurs.
Solution
Use lowercase y for year:

Summary
- When encountering time formatting, it's best to use lowercase y
For example:
date_format(yyyy-MM-dd)
to_date(yyyy-MM-dd)
{{{yesterday %y}}}
Case Two: date_format Function
date_format([Date],"YYYY-MM"), why is December 31, 2019 recognized as "2020-12"?
Cause
The correct usage of date_format is date_format([Date],"yyyy-MM"), not uppercase "YYYY". "YYYY" represents the year based on week cycle. As the last day of 2019, it's actually counted as the first week of 2020.
Solution
Write it as date_format([Date],"yyyy-MM"), and it's fine:

Note
The YYYY issue can be encountered in two places: one is the expression for creating new fields, and the other is time macros. Pay attention to this.
For example: {{{today%yyyy-MM}}} should not be written as {{{today%YYYY-MM}}}.
Uppercase MM and Lowercase mm
When using the to_date() function to convert dates, the numbers don't match. Function writing: to_date([Date]),"yyyymmdd")
Cause:
The lowercase mm here represents minutes. The correct writing is uppercase MM, should be written as: to_date([Date]),"yyyyMMdd")
