SQL Date Formats

SQL supports various date and time data types and formats. The specific date format depends on the database system you are using.

Here are some standard date and time data types and formats in SQL:

• DATE: YYYY-MM-DD
• TIME: HH:MI:SS
• DATETIME: YYYY-MM-DD HH:MI:SS
• TIMESTAMP: YYYY-MM-DD HH:MI:SS
• INTERVAL: This data type stores a duration or interval of time in various formats.

You can use the FORMAT() function to format a date. Here is an example:

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy')

In this example, GETDATE() retrieves the current date and time, and the format string ‘MM/DD/yyyy’ specifies the desired date format.

Here are some date format codes in the format string:

• yyyy: Year with century (e.g., 2023)
• yy: Year without century (e.g., 23)
• MM: Month as a two-digit number (e.g., 02 for February)
• MMM: Abbreviated month name (e.g., Feb)
• MMMM: Full month name (e.g., February)
• dd: Day of the month as a two-digit number (e.g., 03)
• ddd: Abbreviated day name (e.g., Tue)
• dddd: Full day name (e.g., Tuesday)

You can use these codes to create custom date formats that suit your needs. It’s also important to remember that formatting the date can affect the sorting and filtering of the data, so it’s essential to use a consistent format throughout your queries.

SQL Date Formats: Platform-Specific Variations

Amazon Redshift

Redshift mostly adheres to ANSI SQL standards for date formats, but there are some specifics you might want to consider. For example, Redshift provides TO_CHAR function to convert date/time types to string:

SELECT TO_CHAR(current_date, 'DD-Mon-YYYY') as formatted_date 
FROM table;

Snowflake

Snowflake supports a variety of date and time formats. Like Redshift, Snowflake uses the TO_VARIANT and TO_CHAR functions for date formatting:

SELECT TO_VARIANT(current_timestamp(4)) FROM table;
SELECT TO_CHAR(current_date, 'MM-DD-YYYY') 
FROM table;

Google BigQuery

BigQuery uses the FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_DATETIME functions:

SELECT FORMAT_TIMESTAMP('%D', current_timestamp()) as formatted_date 
FROM table;

Power Up Date Formatting with CoginitiScript

In Coginiti, you can modularize your SQL code blocks with CoginitiScript, allowing you to create a single source of truth for your date formats. This ensures that all queries use the same formatting rules, contributing to data consistency across your organization. Let’s see how to implement this with a macro.

Creating a CoginitiScript Macro for Date Formatting

#+macro DateFormat(d)
#+begin
  TO_CHAR({{ d }}, 'MM-DD-YYYY')
#+end

The above macro, DateFormat, uses the TO_CHAR function for the sake of demonstration, but you could adjust this to use functions specific to your database.

To utilize this macro in your SQL code, you can do the following:

SELECT {{ DateFormat('current_date') }} as formatted_date 
FROM table;

This ensures that wherever you need to format a date, you can use the DateFormat macro, which helps maintain consistency and reduces code duplication.

By incorporating this macro, you not only streamline your SQL code but also make it easier for teams to maintain and update date format logic.