Learn SQL for Developers/

...

/

Date Functions

Date Functions

Learn to work with date and time in SQL.

We'll cover the following...

We often need to analyze how data changes over specific periods. For example, we might want to see how many orders are placed each month or track product availability over time. Or perhaps, we need to calculate the delivery dates for orders placed today. Such operations involve working with dates, a critical aspect of database management.

Let's explore essential date functions in SQL. We'll aim to:

  • Understand how to extract parts of a date.

  • Learn how to format dates for better readability.

  • Perform calculations with dates to solve practical problems.

Getting current date and time

In order to get the current date, we can use the following query:

Press + to interact
SELECT CURDATE();

The query above returns the current date in the format YYYY-MM-DD.

In order to get both the current date and time in the format YYYY-MM-DD HH:MM:SS, we can use the following query:

Press + to interact
SELECT NOW();

Sometimes, only current time is needed. We can get that using the following query in the format HH:MM:SS.

Press + to interact
SELECT CURTIME();

The above statements have been demonstrated in the following.

Press + to interact
-- Get the current date
SELECT CURDATE();
-- Get both the current date and the time of the server
SELECT NOW();
-- Get current time of the server
SELECT CURTIME();

Extracting date parts

Extracting specific parts of a date, such as the year, month, or day, is a fundamental operation when working with time-based data. We may want to know, for instance, in which month we got the most orders, or how many orders were placed in a given year.

A few common functions for extracting date parts (in many SQL dialects) include:

  • YEAR(date_column) to get the year.

  • ...