How to Convert String Date With Format in SQLite

Unlike other query languages, SQLite doesn’t support storing the date in a date format. This is one of the disadvantages of SQLite.

A date in a string format may be stored in a default format like YYYY-MM-DD. As STRFTIME doesn’t support any special format for date, you can’t specify the date format during the execution of the query.

In this article, we will see how we can change the date format in string, and also we will see an example to make the topic easier.

For this purpose, we will use a special function, STRFTIME(). This function can take the date, which is in string format, and also in a different date format.

Convert the Date Format Using the Function STRFTIME()

In our example below, we will demonstrate how we can change the date format using the function STRFTIME(). We are going to put the date in string format.

Let’s have a look at the below query.

SELECT STRFTIME('%d/%m/%Y, %H:%M', '2016-04-05 07:30:00');

In the example above, let’s look at the properties below, which you can use with the function in brief.

1. %d - for the day
2. %f - for the fractional seconds like SS.SSS
3. %H - for the hour
4. %j - for the day of the year
5. %J - for the Julian day number
6. %m - for the month
7. %M - for the minute
8. %S - for the seconds
9. %w - for the day of the week
10. %W - for the week of the year
11. %Y - for the year

You can reorganize the format you want inside the STRFTIME() function based on your need.

You will get the below output when you run the above example query.

05/04/2016 07:30

Please note that the queries we provided in this article are written for SQLite.