Hi there,

I have a sqlite Db with a Date field namded Date in the following format...

Friday, February 18, 2022

I need to retrieve data >= date1 and <= date2. I have tried everything I can find in a query, but nothing is working for the date conversion.

Can anyone help me get something like the following...

Select RiseBG, PeakBG from MealData where date >= date1 and <= date2.

I've seen the use of BETWEEN, but not sure if that includes the dates passed into the query or not.

Any help GREATLY appreciated and I thank you in advance.

You would be ahead if you store the date in a sortable form, such as ISO 8601 or one of the numeric forms known to the data and time functions. You can use one of those functions to prettify your date when needed for presentation, but do comparisons in queries much more readily and speedily.

The problem is it's not our data, just need to convert it. I was hoping it was possible in the query.

I guess their date-time format was derived from a date-time picker in a .NET app just saved as a string.

That data is certainly susceptible to conversion. With a few instr(...) and substring(...) function applications, and maybe a willingness to overlook the extraneous day-of-week subfield, it should be simple to extract the components, then use those along with a CASE expression to compute a numeric date or an ISO 8601 formatted date. I shudder to think of putting such a computation in multiple places in a query. It would be better to make a more data-like view or even an intermediate temporary table.

1. Create another column which will have the date stored in a useful format. Julian Day, stored as an integer, would be a good one, and is widely understood and used by programmers.
2. Write software in your favourite language to read the existing date from each row, convert it to the better format, and update the row to set the value in the new column.
3. Change your code so that new values are stored in both date columns. (If you have no plans to add or change rows to that table, you can skip this one.)
4. Next time you create a database, think about this problem before you decide what to store and what format to store it in.

You may find the functions on this page useful. But there's no simple function which will do all the conversion for you.

https://sqlite.org/lang_datefunc.html

Will try this. It is not our Database so we didn't decide to store it this way. We just have to deal with it now :(

Check my recent post in following forum post 4/17/2022, 5:03:31 PM convert into YYYY-MM-DD HH:MM:SS.

Like described there you can use a substr(instr(...)) to get rid of the leading day string and the comma, then a sequence of 12 replace statements to change the month name into a month number and a comma.

Use a replace to change the comma and space into slashes as separators.

Append with || a dummy time like '00:00:00' and continue with my example in that post.

The rest of the work would be what I have shown in my posted example.

Once you understood the principle you will find an easier way for your specific format.

Edit: Attached another proposal of mine (with correction on final ordered output of the ISOnized):

D:\sandbox>sqlite3
SQLite version 3.38.5 2022-05-06 15:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode box
sqlite> with input(txt) as (
   ...>   values('Friday, February 18, 2022')
   ...> ), JSONready as (
   ...> select txt
   ...> , substr(txt,instr(txt,', ')+2) as woDayName
   ...> , replace(substr(txt,instr(txt,', ')+2),', ',',') as singleSep
   ...> , replace(replace(substr(txt,instr(txt,', ')+2),', ',','),' ',',') as homogenized
   ...> , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
   ...>       replace(replace(substr(txt,instr(txt,', ')+2),', ',','),' ',',')
   ...>          ,'January','01'),'February','02'),'March','03'),'April','04'),'May','05'),'June','06')
   ...>          ,'July','07'),'August','08'),'September','09'),'October','10'),'November','11')
   ...>          ,'December','12') as digitized
   ...> , json( '["'|| replace(
   ...>  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
   ...>       replace(replace(substr(txt,instr(txt,', ')+2),', ',','),' ',',')
   ...>          ,'January','01'),'February','02'),'March','03'),'April','04'),'May','05'),'June','06')
   ...>          ,'July','07'),'August','08'),'September','09'),'October','10'),'November','11')
   ...>          ,'December','12')
   ...>                       ,',','","') || '"]' ) as jsonized
   ...>   from input
   ...> )
   ...> select *
   ...> , jsonized->>'$[0]' as Month
   ...> , jsonized->>'$[1]' as Day
   ...> , jsonized->>'$[2]' as Year
   ...> , format('%s-%s-%s', jsonized->>'$[2]', jsonized->>'$[0]', jsonized->>'$[1]') as ISOnized
   ...> from JSONready;
┌───────────────────────────┬───────────────────┬──────────────────┬──────────────────┬────────────┬────────────────────┬───────┬─────┬──────┬────────────┐
│            txt            │     woDayName     │    singleSep     │   homogenized    │ digitized  │      jsonized      │ Month │ Day │ Year │  ISOnized  │
├───────────────────────────┼───────────────────┼──────────────────┼──────────────────┼────────────┼────────────────────┼───────┼─────┼──────┼────────────┤
│ Friday, February 18, 2022 │ February 18, 2022 │ February 18,2022 │ February,18,2022 │ 02,18,2022 │ ["02","18","2022"] │ 02    │ 18  │ 2022 │ 2022-02-18 │
└───────────────────────────┴───────────────────┴──────────────────┴──────────────────┴────────────┴────────────────────┴───────┴─────┴──────┴────────────┘
sqlite>

Another solution of mine using an evaluation of Lua embedded extension function eLua and the substitution using a lambda function with 3 arguments using the pattern matching mechanism of the string.gsub substitution methode for field separation:

D:\sandbox>sqlite3.exe
SQLite version 3.38.5 2022-05-06 15:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load sqlite3.dll
sqlite> .mode box
sqlite> with input(txt) as (
   ...>   values('Friday, February 18, 2022')
   ...> )
   ...> select txt
   ...> ,eLua('return arg[1]', txt )                                                    as LuaEcho
   ...> ,eLua('return (string.gsub(arg[1],".-, (.+) (%d+), (%d+)","%3-%1-%2"))', txt )  as LuaOrdered
   ...> ,eLua('return (string.gsub(arg[1],".-, (.+) (%d+), (%d+)", function(m,d,y)
   ...>             return string.format("%s-%s-%s",y,m,d) end ))', txt )               as LuaFunction
   ...> ,eLua('return (string.gsub(arg[1],".-, (.+) (%d+), (%d+)", function(m,d,y)
   ...>             local NameToNum = { January = "01", February = "02", March = "03"
   ...>                               , April = "04",   May = "05",      June = "06"
   ...>                               , July = "07",    August = "08",   September = "09"
   ...>                               , October = "10", November = "11", December = "12" }
   ...>             return string.format("%s-%s-%s",y,NameToNum[m],d) end ))', txt )    as ISOnized
   ...>   from input;
┌───────────────────────────┬───────────────────────────┬──────────────────┬──────────────────┬────────────┐
│            txt            │          LuaEcho          │    LuaOrdered    │   LuaFunction    │  ISOnized  │
├───────────────────────────┼───────────────────────────┼──────────────────┼──────────────────┼────────────┤
│ Friday, February 18, 2022 │ Friday, February 18, 2022 │ 2022-February-18 │ 2022-February-18 │ 2022-02-18 │
└───────────────────────────┴───────────────────────────┴──────────────────┴──────────────────┴────────────┘
sqlite>

Could you provide a link to this Lua extension?

To add to the other examples - this is pure SQL to read such a bad-formatted date from a table and fix it, or in this case, update it back into the table.

In the example I update a second column, but you can overwrite the origin column too, just make sure to keep backups of the DB before you do.

  -- SQLite version 3.37.2  [ Release: 2022-01-06 ]  on  SQLitespeed version 2.1.3.11.
CREATE TABLE bad_dates(bad_date TEXT, good_date NUMERIC);

INSERT INTO bad_dates(bad_date) VALUES
 ('Friday, February 18, 2022')
,('Friday, February 4, 2022')
,('Previousday, January 1, 2022')
,('Wednesday, may 11, 2022')
,('Sameday, September 30, 2021')
,('Someday, APRIL 2, 2023')
,('Anotherday, December 31, 2022')
;


WITH B1(bd, sdt) AS (
    SELECT bad_date, upper(trim(substr(bad_date,instr(bad_date, ',') + 1))) FROM bad_dates
), B2(bd, sMth, iDay, iYear) AS (
    SELECT bd, trim(substr(sdt, 1, 3)),
           trim(substr(sdt, instr(sdt, ',') - 2, 2)),
	   trim(substr(sdt, instr(sdt, ',') + 1))
      FROM B1
), B3(bd, iYear,iMonth,iDay) AS (
    SELECT bd, iYear, instr('---JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',sMth)/3, iDay
      FROM B2
), B4(bd, sYear, sMonth, sDay) AS (
    SELECT bd, iYear,
           CASE WHEN CAST(iMonth AS INT) > 9 THEN iMonth ELSE '0'||iMonth END,
           CASE WHEN CAST(iDay   AS INT) > 9 THEN iDay   ELSE '0'||iDay   END
      FROM B3
)
UPDATE bad_dates
   SET good_date = (SELECT date(sYear||'-'||sMonth||'-'||sDay) FROM B4 WHERE bd = bad_date)
 WHERE good_date IS NULL
;



SELECT * FROM bad_dates;

  -- bad_date                       |good_date   
  -- -------------------------------|------------
  -- Friday, February 18, 2022      |2022-02-18  
  -- Friday, February 4, 2022       |2022-02-04  
  -- Previousday, January 1, 2022   |2022-01-01  
  -- Wednesday, may 11, 2022        |2022-05-11  
  -- Sameday, September 30, 2021    |2021-09-30  
  -- Someday, APRIL 2, 2023         |2023-04-02  
  -- Anotherday, December 31, 2022  |2022-12-31  

Also, I've gone for clarity rather than compactness - and you can see what happens at each stage by simply selecting from the different stage CTEs (B1, B2.. etc.) such as this example:

WITH B1(bd, sdt) AS (
    SELECT bad_date, upper(trim(substr(bad_date,instr(bad_date,',')+1))) FROM bad_dates
), B2(bd, sMth, iDay, iYear) AS (
    SELECT bd, trim(substr(sdt, 1, 3)),
           trim(substr(sdt, instr(sdt, ',') - 2, 2)),
	   trim(substr(sdt, instr(sdt, ',') + 1))
      FROM B1
)                  -- trimmed off the rest of the statement....
SELECT * FROM B2;

  -- bd                             | sMth|iDay| iYear
  -- -------------------------------|-----|----|------
  -- Friday, February 18, 2022      | FEB | 18 | 2022 
  -- Friday, February 4, 2022       | FEB |  4 | 2022 
  -- Previousday, January 1, 2022   | JAN |  1 | 2022 
  -- Wednesday, may 11, 2022        | MAY | 11 | 2022 
  -- Sameday, September 30, 2021    | SEP | 30 | 2021 
  -- Someday, APRIL 2, 2023         | APR |  2 | 2023 
  -- Anotherday, December 31, 2022  | DEC | 31 | 2022 

THIS WORKED PERFECTLY!!!!!!!

THANK YOU!!