Sqlite get the date part of a DateTime and return as a DateTime, not a string

Question

In Sqlite I want to extract the date and time portions of a DateTime field separately in a view and return them also as a datetime, not strings. I've tried Cast, Date(), datetime(), but they all return strings.

I've read the SQLite documentation and understand how there is not an actual Date data type. Yet a Table field defined as DateTime is able to be parsed as a Date by an Excel query, but calculations on that field are not. I'm trying to do all data prep in the database view.

My data has the following field taken directly from the table definition:

LastModifiedDate datetime  

I want the date (without time) to have the same DateTime data type as LastModifiedDate, not Text, because I use this view in many spreadsheets. I can apply Excel Date functions and formatting to LastModifiedDate field directly as returned from the ODBC query to Excel, and want to do the same to the Date-only part. I don't want to have to put a string-to-date conversion in every spreadsheet when I know it can get the date natively from Sqlite in LastModifiedDate.

SELECT LastModifiedDate, 
       date(LastModifiedDate)            as Datepart, 
       cast(LastModifiedDate as numeric) as Date2 
FROM Transactions

LastModifiedDate              Datepart       Date2
2019-07-28 18:22:38.9165394   2019-07-28     2019

LastModifiedDate in the above query is interpreted in Excel as a date to which date formats and date functions can be applied with no further processing required. Datepart above is returned as Text to Excel, and I can't apply date functions and formats without further pre-processing in Excel. I would like Datepart to be interpreted a date in Excel just as LastModifiedDate is.

Answer 1

I'm looking at the ch-werner.de sqliteodbc-0.9998. It will return an ODBC TIMESTAMP type only if the column decltype starts with timestamp or datetime. It returns ODBC TIME only for decltypes starting with time and ODBC DATE only for decltypes starting with date.

sqlite3 provides this decltype only for result table columns that are direct database column references. So if your SELECT statement has some expression that is more than a plain column reference, the decltype is lost. sqlite3 works like this at least up to version 3.39.0. It is documented.

The CAST expression converts the value of given expression to a storage classes by the determined affinity of the given declared type, but does not assign decltype to the result.

If you want to see the decltypes for query columns, you can use the sqlite3 cli and give it command .stats 2. Then it'll output the column declared types for each statement it executes.

If the decltype is found, the sqliteodbc-0.9998 will always parse string values into ODBC types. If DSN Option JDConv is enabled, it'll also parse floating point julianday values (whether provided as float or a string of a float) into ODBC types and when writing it'll write floating point into database.

If you can afford to change the schema, you can add a generated virtual column. This is cheap in storage, because data is not affected, but it costs when you query the column. This column can calculate other column into the values and decltypes you need for ODBC.

ALTER TABLE data ADD COLUMN
Datepart date AS (date(LastModifiedDate))

Then to get the Datepart, you simply query the column.

SELECT Datepart FROM data