sqlite

SQLite text to date conversion in to another table

I want to copy a table in my SQLite Database, so I have to select CHAR(20) as DATE the value is stored as DD-MM-YYYY in the database

After some reserach i founde DATE() could do it with a string in DD/MM/YYYY so i changed my sql statement to this:

select DATE(replace(date, '-', '/')) as converted, from dashboard_price;

This returns empty, perhaps because the field is named like the function. Or what did I do wrong?

Edit

I updated my sql statement, it looks like this now:

select id, strftime('%d-%m-%Y', date) as converted, price, fiat_id from dashboard_price;

But it still doesn't work.

Solution

  • I want to copy a table in my SQLite Database, so I have to select CHAR(20) as DATE the value is stored as DD-MM-YYYY in the database

    You don't have to have CHAR(20) as the column type. SQLite only has types TEXT, INTEGER, REAL, BLOB and NUMERIC. However, SQLite will allow virtually any column type and will follow a set of rules to determine a column's type affinity, which in itself is just an indication of the column's type.

    With the exception of the special INTEGER PRIMARY KEY (with or without the AUTOINCREMENT keyword) or unless the STRICT table option is used, any type of value can be stored in any type of column.

    After some research I found DATE() could do it with a string in DD/MM/YYYY

    This is not the case. The date function can only work with recognised time-value formats; DD-MM-YYYY is not a recognised time-value format. YYYY-MM-DD is a recognised time-value

    Simple fix

    Now simply to swap (replace) -s with / then as indicated the built in/core function replace could be used.

    But!

    If you want to use the strftime function (or any of the date/time functions) then the value to be enacted upon MUST BE A RECONISED FORMAT; DD-MM-YYYY IS NOT A RECOGNISED FORMAT. However it can be re-arranged to suit (as long as the day and the month parts are always 2 numerics e.g. 01-01-2024 and NOT 1-1-2024 (that complicates matters somewhat))

    However, it is considered much better practice to store dates in a recognised format. This:-

    1. ensures that the data/time functions can manipulate the dates easily, so the data can be output very much as one likes relatively easily and efficiently.
    2. allows the values to be sorted and compared against each other directly
      1. easy to say date BETWEEN a_supplied_start_date_value AND a_supplied_end_date_value
        1. note where both supplied values are in a recognised format
      2. easy to say ORDER BY date

    A Demo

    Perhaps consider the following demo that covers the points made above:-

    DROP TABLE IF EXISTS demo;
/* Create a table with various column types some dubious INTEGER, BLOB and TEXT  not dubious */
CREATE TABLE IF NOT EXISTS demo (
    date whaevertype, 
    od1 INTEGER, 
    od2 blah, 
    od3 blob, 
    od4 TEXT, 
    od5 CHAR(-100)
);
/* Populate the table with :-
    the first row as all blank values,
    the second row with human recognisable dates, the last column with an SQLite date function recognisable date,
    the third row suprious data
    */
INSERT INTO demo VALUES
    ('','','','','',''),
    ('01-01-2024','01-01-2024','01-01-2024','01-01-2024','01-01-2024','2024-01-01'),
    ('01-01-2024',x'f1f2f3f4f5f6f7f8f9','01012024',01-01-2024,'01-$01$-2024','2024-01-01')
;
/* OUTPUT 1: show the type of the value stored in the column*/
SELECT typeof(date) AS t1, typeof(od1) AS t2,typeof(od2) AS t3, typeof(od3) AS t4, typeof(od4) AS t5, typeof(od5) AS t6 
FROM demo;
/* OUTPUT 2: Select data using the replace function to swap hypens for forward slashes */
SELECT *,
    replace(date,'-','/') AS c1,
    replace(od1,'-','/') AS c2,
    replace(od2,'-','/') AS c3,
    replace(od3,'-','/') AS c4,
    replace(od4,'-','/') AS c5,
    replace(od5,'-','/') AS c6
FROM demo ;
/* OUPUT 3: Select data via the date function (same date hanlding if using other date/time functions i.e. NULL if not a recognised date)*/
SELECT *,
    date(date) AS df1,
    date(od1) AS df2,
    date(od2) AS df3,
    date(od3) AS df4,
    date(od4) AS df5,
    date(od5) AS df6
FROM demo;
/* OUTPUT 4: Rearrange a DD-MM-YYYY date to be YYYY-MM-DD and use the date function*/
SELECT *,
    date(substr(date,7,4)||substr(date,3,4)||substr(date,1,2)) AS r1,
    date(substr(od1,7,4)||substr(od1,3,4)||substr(od1,1,2)) AS r2,
    date(substr(od2,7,4)||substr(od2,3,4)||substr(od2,1,2)) AS r3,
    date(substr(od3,7,4)||substr(od3,3,4)||substr(od3,1,2)) AS r4,
    date(substr(od4,7,4)||substr(od4,3,4)||substr(od4,1,2)) AS r5,
    date(substr(od5,7,4)||substr(od5,3,4)||substr(od5,1,2)) AS r6
FROM demo
;
/* OUTPUT 5: Handling multiple date types (aka what is best avoided)*/
SELECT
    CASE 
        WHEN substr(date,3,1) = '-' AND substr(date,6,1) = '-' THEN
            'F1 Date found as DD-MM-YYYY format; '||strftime('%d-%m-%Y %H:%M',substr(date,7,4)||substr(date,3,4)||substr(date,1,2))
        WHEN substr(date,3,1) = '/' AND substr(date,6,1) = '/' THEN
            'F2 Date found as DD/MM/YYYY format; '||strftime('%d-%m-%Y %H:%M',replace(substr(date,7,4)||substr(date,3,4)||substr(date,1,2),'/','-'))
        
        WHEN substr(date,5,1) = '-' AND substr(date,8,1) = '-' THEN
            'F3 Date found as YYYY-MM-DD format; '||strftime('%d-%m-%Y %H:%M',date)
        ELSE 'OOOOPS UNEXPECTED DATE' 
    END
    AS date,
    CASE 
        WHEN substr(od1,3,1) = '-' AND substr(od1,6,1) = '-' THEN
            'F1 Date found as DD-MM-YYYY format; '||strftime('%d-%m-%Y %H:%M',substr(od1,7,4)||substr(od1,3,4)||substr(od1,1,2))
        WHEN substr(od1,3,1) = '/' AND substr(od1,6,1) = '/' THEN
            'F2 Date found as DD/MM/YYYY format; '||strftime('%d-%m-%Y %H:%M',replace(substr(od1,7,4)||substr(od1,3,4)||substr(od1,1,2),'/','-'))
        
        WHEN substr(od5,5,1) = '-' AND substr(od5,8,1) = '-' THEN
            'F3 Date found as YYYY-MM-DD format; '||strftime('%d-%m-%Y %H:%M',od5)
        ELSE 'OOOOPS UNEXPECTED DATE' 
    END
    AS od1,
    CASE 
        WHEN substr(od5,3,1) = '-' AND substr(od5,6,1) = '-' THEN
            'F1 Date found as DD-MM-YYYY format; '||strftime('%d-%m-%Y %H:%M',substr(od5,7,4)||substr(od5,3,4)||substr(od5,1,2))
        WHEN substr(od5,3,1) = '/' AND substr(od5,6,1) = '/' THEN
            'F2 Date found as DD/MM/YYYY format; '||strftime('%d-%m-%Y %H:%M',replace(substr(od5,7,4)||substr(od5,3,4)||substr(od5,1,2),'/','-'))
        
        WHEN substr(od5,5,1) = '-' AND substr(od5,8,1) = '-' THEN
            'F3 Date found as YYYY-MM-DD format; '||strftime('%d-%m-%Y %H:%M',od5)
        ELSE 'OOOOPS UNEXPECTED DATE' 
    END
    AS od5  
FROM demo;
/* Cleanup the demo environment*/

    First the table is dropped, just in case it exists and is then created. Noting the use of various column types some with dubious names.

    Some data is loaded. 3 rows.

    1. The first row has an empty string for all the columns (showing that the column type does not inhibit the such values being stored in columns that should probably not contain an empty string)
      1. note this is on purpose; obviously this practice would probably be inhibited by either verifying the data before insertion or inhibited when inserting via suitable constraints.
    2. The second row inserts dates in the DD-MM-YYYY format, with the exception of the od5 column which has a date in the YYYY-MM-DD format (as is considered a better practice than the more awkward/less efficient (handling wise as opposed to storage wise) DD-MM-YYYY format)
    3. The third row is made up of various values/types expected to cause unusual results (the date and od5 columns not so).
      1. od1 is a BLOB type,
      2. od2 is the date in DDMMYYYY format as a string/TEXT type
      3. od3 is the date in DDMMYYYY format BUT as a numeric
      4. od4 is the date in a weird/confusing/mistake DD-$MM$-YYYY format.

    Results

    The 1st query extracts the type of the specific column/row combination using the typeOf function. The output/result being:-

    Q1

    The third row has different types in the 2nd, 3rd and 4th columns to the preceding rows. i.e. showing that a column type can contain values of other types. e.g. column od2 with a defined type of blah (which ends up being a NUMERIC type affinity) can hold a value that is a BLOB

    The 2nd query shows the results of the simplest solution (noting that the 2nd row is the best reflection of the issue posed in the question):-

    Q2

    The 3rd query demonstrates the issue you have encountered with the date functions i.e. NOT accepting DD-MM-YYYY as a date (as is documented):-

    Q3

    The 4th query shows how re-arranging the value from DD-MM-YYYY to YYYY-MM-DD format then allows the date function to produce date orientated acceptable results:-

    Q4

    The 5th query shows a mix of stored date types could be handled (at least partially) albeit it not recommended to follow such a path:-

    Q5

    The 3rd row!!!! The 3rd row shows what some may consider curious results. Explanation of these is beyond the scope of the question.