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.

%d	Day of month: 00
%f	Fractional seconds: SS.SSS
%H	Hour: 00-24
%j	Day of year: 001-366
%J	Julian day number
%m	Month: 01-12
%M	Minute: 00-59
%s	Seconds since 1970-01-01
%S	Seconds: 00-59
%w	Day of week 0-6 with Sunday==0
%W	Week of year: 00-53
%Y	Year: 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