Valid Format String Substitutions for the SQLite Strftime() Function

The table below contains the valid format string substitutions that you can use with the strftime() function in SQLite.

%dDay of month: 00
%fFractional seconds: SS.SSS
%HHour: 00-24
%jDay of year: 001-366
%JJulian day number
%mMonth: 01-12
%MMinute: 00-59
%sSeconds since 1970-01-01
%SSeconds: 00-59
%wDay of week 0-6 with Sunday==0
%WWeek of year: 00-53
%YYear: 0000-9999
%%%

How do These Work?

In SQLite, the strftime() function returns a date/time value in a given format.

You specify the format at the time you call the function. You do this by providing a format string, which consists of one or more “format string substitutions”.

Each format string substitution is like a placeholder for a given date part. For example, %m is for the month, %Y is for year, etc.

Example

Here’s an example to demonstrate:

.mode line
SELECT 
  strftime('%Y-%m-%d', 'now') AS "Date",
  strftime('%H:%M:%S', 'now') AS "Time",
  strftime('%Y-%m-%d %H:%M:%S', 'now') AS "Date & Time",
  strftime('%d/%m/%Y', 'now') AS "Date 2",
  strftime('%s', 'now') AS "Epoch Time",
  strftime('%J', 'now') AS "Julian Day";

Result:

       Date = 2020-04-27
       Time = 00:11:45
Date & Time = 2020-04-27 00:11:45
     Date 2 = 27/04/2020
 Epoch Time = 1587946305
 Julian Day = 2458966.508165996