Convert a Date to a String in Sqlite

Question

Is there a way to convert a date to a string in Sqlite? For example I am trying to get the min date in Sqlite:

SELECT MIN(StartDate) AS MinDate FROM TableName

I know in SQL Server I would use the SQL below to accomplish what I am trying to do:

SELECT CONVERT(VARCHAR(10), MIN(StartDate), 101) AS MinDate FROM TableName

Thanks!

Answer 1

Try this:

SELECT MIN(DATE('your-date-here')) as MinDate FROM TableName

And make sure your-date-here follows a well understood format by SQLite (See the Time String. section in the documentation)

Answer 2

SQLite doesn't have a date type. What CURRENT_TIMESTAMP actually returns is a string in the format YYYY-MM-DD HH:MM:SS. The date/time formatting/calculation functions accept strings in this format, or alternatively, numbers, which are interpreted as Julian dates unless you use the 'unixepoch' modifier.

So, "converting a date to a string" depends on how you're storing your dates:

• If you're storing them as Julian dates, use DATE(YourDateColumn).
• If you're storing them as Unix times, use DATE(YourDateColumn, 'unixepoch').
• If you're storing them as ISO 8601 strings, then, well you don't have to do anything. Unless you want a string in a different format, in which case you should use the STRFTIME function.
• If you're storing them as some other string format, you really should consider using one of the supported formats so you can use the date/time functions.

In all three date formats, chronological order is the same as lexicographical order, so the MIN function works as expected.