SQLite DateTime() Function

  Total Views : 20
  Comments : 0
 
  Keywords : SQLite datetime() query example, Use sqlite datetime() function to return date and time from given string with example, SQLite datetime() function with examples

Here we will learn SQLite datetime() function with example and how to use SQLite datetime() function to get date and time from given string with example

SQLite datetime() Function

In SQLite datetime() function is used to translate give string into date and time.

Syntax of SQLite datetime() Function

Following is the syntax of SQLite datetime() function to get date and time from given string.

 

datetime(datetimestring, [modifier1, modifier2…, modifierN])

The SQLite datetime() function takes datetimestring & one or more modifier values and returns date and time in YYYY-MM-DD HH:MM:SS format. It is important to note that this function will return TEXT as an output.

 

In above SQLite date() syntax the 1st argument datetimestring will accept the values in following formats.

 

FormatExample
now 2016-08-30
YYYY-MM-DD 2016-08-30
YYYY-MM-DD HH:MM 2016-08-30 18:47
YYYY-MM-DD HH:MM:SS 2016-08-30 18:47:56
YYYY-MM-DD HH:MM:SS.sss 2016-08-30 18:47:56.235
YYYY-MM-DDTHH:MM T literal is used to separate date & time
YYYY-MM-DDTHH:MM:SS T literal is used to separate date & time
YYYY-MM-DDTHH:MM:SS.sss T literal is used to separate date & time
HH:MM 18:50
HH:MM:SS 06:52:25
HH:MM:SS.sss 06:26:56.123
DDDDDDD Julian date number
DDDDDDD.ddddddd Julian date number with fractional part

The 2nd parameter modifiers (modifier1, modifier2, … etc.) will accept the values in following format.

 

FormatExample
XX days 02 days
XX hours 10 hours
XX minutes 15 minutes
XX.XXXX seconds 10.15 seconds
XX months 04 months
XX years 02 years
start of month start of month
start of year start of year
start of day start of day
weekday X weekday 2
unixepoch unixepoch
localtime localtime
utc utc

Example of SQLite datetime() Function

We will see how to use SQLite datetime() function with different examples.

 

Following is the example of sqlite datetime() function to return current date and time in YYYY-MM-DD HH:MM:SS format.

 

sqlite> SELECT datetime('now') as 'Current date & time';

 

Current date & time

-------------------

2016-08-31 06:38:14

In case if we want to get date and time preceded by 564 minutes from the 1st day of the current month we need to write SQLite query like as shown following.

 

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

 

Date and Time

-------------------

2016-08-01 09:24:00

Now we will see how to use SQLite datetime() function with tables data in database for the create one table called Employee using following query. 

 

CREATE TABLE Employee

(eid INTEGER PRIMARY KEY AUTOINCREMENT,

ename TEXT,

joining_date TEXT,

salary REAL);

Let’s insert some records to Employee table using following statements.

 

INSERT INTO Employee(ename,joining_date,salary)

VALUES ('Rati',date('now','start of month'),5200),

('Umang',date('now','start of year'),5700),

('Prachi',date('now','start of year','-7 months'),5500),

('Ashmite',date('now','start of year','-32 days'),7000);

Now, see the records of Employee table with SQLite SELECT statement.

 

sqlite> SELECT * FROM Employee;

 

eid         ename       joining_date  salary

----------  ----------  ------------  ----------

1           Rati        2016-08-01    5200.0

2           Umang       2016-01-01    5700.0

3           Prachi      2015-06-01    5500.0

4           Ashmite     2015-11-30    7000.0

Now write SQLite query like as shown following to get employees whose joining date more than 6 months and eligible for Appraisal cycle.

 

SELECT eid, ename, joining_date, salary, date(joining_date,'6 months') as 'Appraisal date' FROM Employee;

When we run above query we will get result like as shown following.

 

eid         ename       joining_date  salary      Appraisal date

----------  ----------  ------------  ----------  --------------

1           Rati        2016-08-01    5200.0      2017-02-01

2           Umang       2016-01-01    5700.0      2016-07-01

3           Prachi      2015-06-01    5500.0      2015-12-01

4           Ashmite     2015-11-30    7000.0      2016-05-30

This is how we can use SQLite datetime() function to get date and time from given date and time string based on our requirements.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User