SQLite: now Function

This SQLite post explains how to use the SQLite now function (ie: timestring parameter) with syntax and examples.

Description

In SQLite, there isn't a function called the now function, but rather "now" is a timestring parameter that is used in various SQLite functions to retrieve the current date and time. This may seem a bit confusing at first, since other SQL databases have a built-in function whose purpose is to return the current date and time. SQLite does this differently.

We will cover 3 syntaxes that show how to use the "now" timestring in SQLite.

Syntax

The syntax for the now function (ie: timestring parameter) in SQLite is:

date('now')

OR

time('now')

OR

strftime(format, 'now')

Examples:
strftime('%Y-%m-%d','now')
strftime('%Y-%m-%d %H-%M','now')
strftime('%Y-%m-%d %H-%M-%S','now')

Parameters or Arguments

format

format	Explanation
%Y	Year as 4 digits (0000 to 9999)
%W	Week of year (00 to 53)
%w	Day of week (0 to 6, where 0 is Sunday)
%m	Month of year (01 to 12)
%d	Day of month (00 to 31)
%H	Hour (00 to 24)
%M	Minute (00 to 25)
%S	Seconds (00 to 59)
%s	Seconds since 1970-01-01
%f	Fractional seconds (SS.SSS)
%j	Day of year (001 to 366)
%J	Julian day as a numeric value

Note

When using the date function syntax, the current date is displayed as a 'YYYY-MM-DD' format.
When using the time function syntax, the current time is displayed as a 'HH-MM-SS' format.
When using the strftime function syntax, the current date/time is displayed using the format provided. For example, a format of '%Y-%m-%d' would return the current date displayed as 'YYYY-MM-DD'.

Applies To

The now function can be used in the following versions of SQLite:

SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.x, SQLite 3.6.x

Example

Let's look at some SQLite now function examples and explore how to use the now function in SQLite. You can use the built-in functions to retrieve the current date, current time, or current date/time values.

Current Date Example

You could retrieve the current date in SQLite using the "now" timestring with either the date function or strftime function as follows:

sqlite> SELECT date('now');
Output: '2014-10-18'               (formatted as YYYY-MM-DD)

sqlite> SELECT strftime('%Y-%m-%d','now');
Output: '2014-10-18'               (formatted as YYYY-MM-DD)

Current Time Example

You could retrieve the current time in SQLite using the "now" timestring with either the time function or strftime function as follows:

sqlite> SELECT time('now');
Output: '11-13-52'                 (formatted as HH-MM-SS)

sqlite> SELECT strftime('%H-%M-%S','now');
Output: '11-13-52'                 (formatted as HH-MM-SS)

sqlite> SELECT strftime('%H-%M-%f','now');
Output: '11-13-52.990'            (formatted as HH-MM-SS.SSS)

sqlite> SELECT strftime('%H-%M','now');
Output: '11-13'                    (formatted as HH-MM)

Current Date and Time Example

You could retrieve the current date and time in SQLite using the "now" timestring with the strftime function as follows:

sqlite> SELECT strftime('%Y-%m-%d %H-%M','now');
Output: '2014-10-18 11-13'         (formatted as YYYY-MM-DD HH-MM)

sqlite> SELECT strftime('%Y-%m-%d %H-%M-%S','now');
Output: '2014-10-18 11-13-52'      (formatted as YYYY-MM-DD HH-MM-SS)

sqlite> SELECT strftime('%Y-%m-%d %H-%M-%f','now');
Output: '2014-10-18 11-13-52.990'  (formatted as YYYY-MM-DD HH-MM-SS.SSS)

Sqlite Now Function