Skip to main content

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

image.png

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:

image.png

Summary

  1. 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:

image.png

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")

image.png