SQLite Date Time Functions Examples

SELECT strftime('%Y', trxDateTime) as sYear 
FROM trx_log

This is the SQL for selecting year part of a date time column from an SQLite data table. For more explanation & examples, please keep reading below. 

Computer time

We have an SQLite database table named 'trx_log'. This table contains a string date time field trxDateTime. It also contains other columns like trxTargetNumber, trxAmount, trxStatus. A snapshot of table schema is given below, some sample data is also included.

Example SQLite Table for query

All fields other than trxAmount are string, only trxAmount which represents a superficial transaction amount is a number type.

SQLite Year calculation function

Sqlite provides strftime function to extract specific part from a date time string. We will use it to find out year in SQLite. Sample SQL query code is given below:

SELECT strftime('%Y', trxDateTime) as sYear 
FROM trx_log

This query will return four rows of data (based on snapshot shown earlier) each containing 2014 and labeled sYear.
The first parameter here is a format string, in this format string %Y represents a year.
Please notice that the Y here is a capital letter.

How to make where clause based on year in SQLite SQL?

You will get the year part of a date using strftime function and then make a decision based on that. For example, if we want data from year 2014 we can use following query.

SELECT strftime('%Y', trxDateTime) as sYear 
FROM trx_log WHERE  strftime('%Y', trxDateTime)='2014'  

Same technique can be used to group data based on year as well. Use of this technique to group by data is well demonstrated in following post:
http://aprogrammersday.blogspot.com/2014/02/group-by-month-or-year-and-sum-in-sqlite.html

SQLite month calculation function

The strftime function can also be used to find out the month section from a date time string column. The exact SQLite SQL query for finding the month is given below

SELECT strftime('%m', trxDateTime) as sMonth 
FROM trx_log

The query mentioned above will return four rows of data (based on snapshot shown earlier) containing 02 as month. It is worth mentioning again that the m in this case will be a small letter. Capital M will cause the SQLite engine to return minutes instead of month.

How to make where clause based on month in SQLite SQL?

It is very easy to use a month field as basis of a where query or group by operation in SQLite. We will use the same strftime function again. An example SQ< query is shown below.

SELECT strftime('%m', trxDateTime) as sYear 
FROM trx_log WHERE  strftime('%m', trxDateTime)='02'  

Please note that SQLite will return months between January to September as two digits where the leading digit will be 0 always. For months December to 

SQLite day calculation function

How to find a day from SQLite table using SQL? Use the strftime function and supply appropriate formatting string in first parameter. The SQLite SQL query for finding the month is given below:

SELECT strftime('%d', trxDateTime) as sDay 
FROM trx_log

How to make where clause based on month in SQLite SQL?

You can also use strftime SQL(Structured Query Language) function to filter data and group data it by certain values of a specific day. I showed you earlier how to find out the day from a SQLite date field. How to use it in a select statement in conjunction with a where clause is shown below:

SELECT strftime('%d', trxDateTime) as sYear 
FROM trx_log WHERE  strftime('%d, trxDateTime)='02'