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.
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:-
date BETWEEN a_supplied_start_date_value AND a_supplied_end_date_value
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.
Results
The 1st query extracts the type of the specific column/row combination using the typeOf
function. The output/result being:-
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
INTEGER PRIMARY KEY
, or that the table has the STRICT
option; i.e. exceptions to this flexibilityThe 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):-
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):-
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:-
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:-
The 3rd row!!!! The 3rd row shows what some may consider curious results. Explanation of these is beyond the scope of the question.