SQLite datetime() function

Description

The SQLite datetime() function returns "YYYY-MM-DD HH:MM:SS".

Syntax:

datetime(timestring, modifier, modifier, ...)

Example-1:

If you want to get the current date and time the following SQL can be used.

SELECT datetime('now') as "Current Date and Time";

Here is the result.

Current Date and Time
---------------------
2014-10-31 11:31:52

Example-2:

If you want to get return the time portion from the given date-time value 2014-10-07 15:45:57.005678, the following SQL can be used.

sqlite> SELECT time('2014-10-07 15:45:57.005678');

Here is the result.

time('2014-10-07 15:45:57.005678')
----------------------------------
15:45:57

Example-3:

If you want to get time preceded by 564 minutes form the 1st day of the current month, the following SQL can be used.

SELECT datetime('now','start of month','564 minutes') as "Date and Time";

Here is the result.

Date and Time
-------------------
2014-10-01 09:24:00

Example-4:

Sample table: job_history

If we want to find date of extension for all the employees getting 3 months more after their term end, the following SQL can be used.

SELECT employee_id,job_id,department_id,start_date,end_date,
date(end_date,'3 months') as "Extension upto" 
FROM job_history;

Here is the result.

employee_id  job_id      department_id  start_date  end_date    Extension upto
-----------  ----------  -------------  ----------  ----------  --------------
102          IT_PROG     60             1993-01-13  1998-07-24  1998-10-24
101          AC_ACCOUNT  110            1989-09-21  1993-10-27  1994-01-27
101          AC_MGR      110            1993-10-28  1997-03-15  1997-06-15
201          MK_REP      20             1996-02-17  1999-12-19  2000-03-19
114          ST_CLERK    50             1998-03-24  1999-12-31  2000-03-31
122          ST_CLERK    50             1999-01-01  1999-12-31  2000-03-31
200          AD_ASST     90             1987-09-17  1993-06-17  1993-09-17
176          SA_REP      80             1998-03-24  1998-12-31  1999-03-31
176          SA_MAN      80             1999-01-01  1999-12-31  2000-03-31
200          AC_ACCOUNT  90             1994-07-01  1998-12-31  1999-03-31

Previous: TIME
Next: JULIANDAY