How to Format a Datetime in SQLite

Problem:

You want to format a datetime column or value in SQLite.

Example:

Our database has a table named ticket with data in the columns passenger_ID, train_number, and sale_datetime.

For each ticket sale, let’s get the passenger ID, train number, and the sale date and time. We want to format the sale date to show the day, month, and year separated by slashes (/), with a comma between the year and the time. We only want the hour and minute of the sale.

Here is the query you would write:

Solution:

SELECT 
  passenger_ID,
  train_number,
  STRFTIME('%d/%m/%Y, %H:%M', sale_datetime)
  AS sale_formatted_datetime 
FROM ticket;

Here’s the result of the query:

Discussion:

Use the STRFTIME() function to format date/time/datetime data in SQLite. This function takes two arguments. The first argument is a format string containing the date/time part pattern. In our example, we use the format string %d/%m/%Y, %H:%M. In this string:

• %d is a two-digit day.
• %m is a two-digit month.
• %Y is a four-digit year.
• %H is a two-digit hour.
• %M is a two-digit minute.

You can find more date and time specifiers in the SQLite documentation.

The second argument is the time/date/datetime value to format. This can be an expression returning a time/date/datetime value or the name of a time/date/datetime column. (In our example, it is the column sale_datetime.)

STRFTIME() returns the formatted date and time. In our example, the formatted date and time doesn’t contain seconds and the date parts are separated by slashes (/) instead of dashes (-).