Back to list SQLite How to Format a Datetime in SQLite Database: SQLite Operators:STRFTIME() Problem: You want to format a datetime column or value in SQLite. Example: Our database has a table named ticket with data in the columns passenger_ID, train_number, and sale_datetime. passenger_IDtrain_numbersale_datetime 245NX45052019-10-17 12:56:30 127RG34032019-10-17 11:45:23 567FH45052019-10-18 12:40:32 230AP67002019-10-18 13:20:20 118BB34032019-10-18 13:13:13 For each ticket sale, let’s get the passenger ID, train number, and the sale date and time. We want to format the sale date to show the day, month, and year separated by slashes (/), with a comma between the year and the time. We only want the hour and minute of the sale. Here is the query you would write: Solution: SELECT passenger_ID, train_number, STRFTIME('%d/%m/%Y, %H:%M', sale_datetime) AS sale_formatted_datetime FROM ticket; Here’s the result of the query: passenger_IDtrain_numbersale_formatted_datetime 245NX450517/10/2019, 12:56 127RG340317/10/2019, 11:45 567FH450518/10/2019, 12:40 230AP670018/10/2019, 13:20 118BB340318/10/2019, 13:13 Discussion: Use the STRFTIME() function to format date\time\datetime data in SQLite. This function takes two arguments. The first argument is a format string containing the date/time part pattern. In our example, we use the format string ‘%d/%m/%Y, %H:%M'. In this string: %d is a two-digit day. %m is a two-digit month. %Y is a four-digit year. %H is a two-digit hour. %M is a two-digit minute. You can find more date and time specifiers in the SQLite documentation. The second argument is the time/date/datetime value to format. This can be an expression returning a time/date/datetime value or the name of a time/date/datetime column. (In our example, it is the column sale_datetime.) STRFTIME() returns the formatted date and time. In our example, the formatted date and time doesn’t contain seconds and the date parts are separated by slashes (/) instead of dashes (-). Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Add a Month to a Date in SQLite How to Get the Current Time in SQLite How to Get the Current Date in SQLite Tags: SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.