Depending on the country, culture or data source, dates can be written in a multitude of ways. To simplify data processing, it is essential to standardize them. So how do you standardize dates? Thanks to the computer language SQL, it's possible to use the DATEFORMAT SQL query.
Standardizing data to make better use of it
Date standardization fits in perfectly with the standardization of your database data.
Organizations have an ever-increasing volume of data at their disposal. But as the sources and formats are extremely varied, it can sometimes be difficult to exploit them. So, to bring more value from data, it’s vital to harmonize it.
To achieve this standardization, organizations need to define a data governance strategy.
Uniform dates
If data standardization is crucial for companies, it’s even more important for dates. All data is integrated on a specific day and time. Knowing this date can have an impact on decision-making. It is therefore essential that they are harmonized throughout the organization.
In practice, however, this is far from always being the case.
Firstly, because data comes from a multitude of sources (social networks, websites, reports, etc.).
Secondly, because each country has its own formatting. For example, April 18, 2023 will be written differently in different countries:
- in France: 18/04/2023
- United States: 04/18/2023
- China: 2023/18/04
Between the diversity of data sources and cultures, the format of dates is very often heterogeneous. This is why the DATEFORMAT SQL query is so useful. Its aim is to provide all database users with a date format that is easy to read and understand by all. So how does it work?
How does a DATEFORMAT-SQL query work?
The DATE_FORMAT SQL query allows you to format a date in a specific format. This is the function of choice if you want to standardize dates across all your data.
Here’s how it works:
SELECT DATE_FORMAT(date,format)
Formats
For the format, you can choose a character string from the list below:
%a: abbreviated weekday name (Sun, Mon, … Sat)
%b: abbreviated month name (Jan, Feb, … Dec)
%c: month number (0, 1, 2, … 12)
%D: day number, with English suffix (0th, 1st, 2nd, 3rd, …)
%d: number of the day of the month, to 2 decimal places (00..31)
%e: number of the day of the month (0..31)
%f: microseconds (000000..999999)
%H: hour (00..23)
%h: hour (01..12)
%I: hour (01..12)
%i: minutes (00..59)
%j: day of year (001..366)
%k: hour (0..23)
%l: hour (1..12)
%M: month name in English (January, February, …December)
%m: month (00..12)
%p: AM or PM
%r: time in 12-hour format (hh:mm:ss followed by AM or PM)
%S: seconds (00..59)
%s: seconds (00..59)
%T: time in 24-hour format (hh:mm:ss)
%U: Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u: Week (00..53), for which Monday is the first day of the week
%V : Week (01..53), for which Sunday is the first day of the week (used with %X)
%v: Week (01..53), for which Monday is the first day of the week; (used with %x)
%W: name of the day of the week (Sunday, Monday, … Saturday)
%w: number of the day of the week (0=Sunday, 1=Monday, … 6=Saturday)
%X: number of the week of the year with 4 digits, for which Sunday is the first day of the week (used with %V)
%x: number of the week of the year with 4 digits, for which Monday is the first day of the week (used with %v)
%Y: year, numeric (with 4 digits)
%y: year, numeric (2-digit)
4 examples of date format with SQL query
The wide variety of formats suggested above enables you to use the combinations that seem most relevant to your organization (particularly with regard to the cultural habits of your employees).
To help you understand, here are a few examples of how to use the DATEFORMAT SQL query:
SELECT DATE_FORMAT(“2023-01-01”, “%D %b %Y”) -> 1st Jan 2023
SELECT DATE_FORMAT(“2023-01-01”, “%M %d %Y”) -> January 01 2023
SELECT DATE_FORMAT(“2023-01-01”, “%W %M %e %Y”) -> Sunday January 1 2023
SELECT DATE_FORMAT(“2023-01-01”, “%d/%m/%Y”) -> 01/01/2023
To promote data standardization across the organization, we recommend that you specify the correct format as part of your data governance strategy.
What are the other date management functions?
In addition to the DATEFORMAT SQL query, you can also use other date functions as SQL queries. These are as follows:
- NOW(): this returns the current date and time in YYYYMMDDHHMMSS or YYYY-MM-DD:HH:SS format.
- CURDATE() : displays the current date in YYYYMMDD or YYYY-MM-DD format.
- CURTIME(): this gives the current time in HHMMSS or HH-MM-SS format.
- DATE_ADD: this adds a time interval to a date (such as a microsecond, a second, an hour, a day, a week, a year, etc.).
- DATE_SUB: conversely, you subtract a time interval from a date.
- DATEDIFF: this SQL query allows you to calculate the number of days between two distinct dates.
- SET DATE FORMAT: this allows you to obtain the date in day/month/year format.
Things to remember
- The DATEFORMAT SQL query is used to standardize dates within a database.
- A multitude of formats can be used. To be defined as part of your data governance strategy.
- This plays a key role in cleansing and harmonizing data.
