SQLite strftime() function

Description

The SQLite strftime() function returns the date formatted according to the format string specified in argument first. The second parameter is used to mention the time string and followed by one or more modifiers can be used to get a different result.

Syntax:

strftime(format, timestring, modifier, modifier, ...)

Example-1:

If you want to extract the Year Month and Day for the current date, the following SQL can be used.

SELECT strftime('%Y %m %d','now');

Here is the result.

strftime('%Y %m %d','now')
--------------------------
2014 10 31

Here in the above example shows that the year, month and day part have been extracted from the current date in text format.

Example-2:

If you want to extract the Hour Minute Second and milliseconds from the current datetime, the following SQL can be used.

SELECT strftime('%H %M %S %s','now');

Here is the result.

strftime('%H %M %S %s','now')
-----------------------------
12 40 18 1414759218

Here in the above example shows that the year, month and day part have been extracted from the current date in text format.

Example-3:

Compute the number of seconds since a particular moment in 2014.

SELECT strftime('%s','now') - strftime('%s','2014-10-07 02:34:56');

Here is the result.

strftime('%s','now') - strftime('%s','2014-10-07 02:34:56')
-----------------------------------------------------------
2110042

Example-4:

Sample table: job_history

If we want to find out the year, month and day value of start_date from the table job_history, the following SQL can be used.

SELECT start_date,strftime('%Y',start_date) as "Year",
strftime('%m',start_date) as "Month",
strftime('%d',start_date) as "Day"
FROM job_history;

Here is the result.

start_date  Year        Month       Day
----------  ----------  ----------  ------
1993-01-13  1993        01          13
1989-09-21  1989        09          21
1993-10-28  1993        10          28
1996-02-17  1996        02          17
1998-03-24  1998        03          24
1999-01-01  1999        01          01
1987-09-17  1987        09          17
1998-03-24  1998        03          24
1999-01-01  1999        01          01
1994-07-01  1994        07          01

Example-5:

Sample table: job_history

If we want to find out the year, month and day value of start_date for those employees who joined in 1 quarter from the table job_history, the following SQL can be used.

SELECT start_date,strftime('%Y',start_date) as "Year",
strftime('%m',start_date) as "Month",
strftime('%d',start_date) as "Day"
FROM job_history
WHERE strftime('%m',start_date)
IN('01','02','03');

Here is the result.

start_date  Year        Month       Day
----------  ----------  ----------  ------
1993-01-13  1993        01          13
1996-02-17  1996        02          17
1998-03-24  1998        03          24
1999-01-01  1999        01          01
1998-03-24  1998        03          24
1999-01-01  1999        01          01

Previous: JULIANDAY
Next: Create, Drop views