SQLite is a popular relational database management system that is widely used in many applications due to its lightweight and portable nature. One of the many functions available in SQLite is the strftime() function, which is used to format date and time values in a specific format.
Syntax
strftime(format, time-value, modifier, modifier, ...)
The strftime() function takes two arguments: a format string and a date/time value. The format string specifies how the date/time value should be formatted, and the date/time value is the input value that should be formatted. The resulting output is a string that represents the formatted date/time value.
The format string contains special format specifiers that are replaced with corresponding values from the date/time value. Some of the common format specifiers used in SQLite’s strftime() function are:
%Y – year (four digits)
%m – month (01-12)
%d – day of the month (01-31)
%H – hour (00-23)
%M – minute (00-59)
%S – second (00-59)
Examples
For example, the following SQL query uses the strftime() function to format the current date/time value as a string in the format of “YYYY-MM-DD HH:MM:SS”:
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');
This query will return a string like “2023-04-19 10:30:15”, which represents the current date and time in the specified format.
Another example is formatting a date stored in a table column. Suppose you have a table called orders with a column order_date that stores the order date in the format “YYYY-MM-DD”. You can use the strftime() function to convert this date to a different format, such as “Month Day, Year”, by running the following SQL query:
SELECT
strftime('%m/%d/%Y', order_date) AS formatted_date
FROM orders;
This query will return a result set with a column called formatted_date, which contains the order date in the format “MM/DD/YYYY”.
In addition to these basic format specifiers, SQLite’s strftime() function also supports many other format specifiers for formatting date and time values, such as timezone information, day of the week, and more. By using these format specifiers, you can format date and time values in any way you want in SQLite.