SQLQuery with localized date format

No need for a screenshot.

Let's say my regional settings are set to this date time format: DD/MM/YYYY HH:MM:ss

Date time are stored in ISO8601 format by default when using SQLite : YYYY-MM-DD HH:MM:ss

Using a table grid to get a date time value returns the date in my regional settings: DD/MM/YYYY HH:MM:ss, if I change my regional settings, the results are displayed using these new settings.

Using an SQL query without forcing the date time format returns the ISO8601 date time: YYYY-MM-DD HH:MM:ss

unless someone knows a script that would force the date time to display using the regional settings.

My point is that I am a European living in the USA and their date time format is different compared to many countries and I am developing an app not only for the US but also for other countries and some of my grids require SQLqueries that cannot be made using the tablegrid default queries (i.e: complex joins). Not to mention that date separators differ from country to country: France is dd/mm/yyyy, Sweden is yyyy-mm-dd, Germany is dd.mm.yyyy, USA is mm/dd/yyyy and... the time in the US is in 12 hours format with AM and PM.

I have actually sent a screenshot in my previous answer to show that using any kind of datetostr variance does not help to make "universal" application, I wish I knew what is behind the tablegrid auto display query for the dates as it uses the regional settings every time.

I don't think this is exactly possible using SQLite.

I think the only way to do what you want, is to use MVD to interrogate Windows for the locale code, and then use a SQL iif.... or CASE function to then select the required SQL strftime formatting function.

As an aside I think SQL Server can do what you want.

Naturally Sparrow I'm having difficulty getting my registry function to work, but I'll post my general idea.

Registry function.

function ReadReg(thekey, value: string): string;
var
  Rg: TRegistry;
begin
  //Create the Object
  Rg := TRegistry.Create;
  with Rg do
  begin    
    RootKey := HKEY_LOCAL_MACHINE;

   //False = no write ability

   if OpenKey(theKey, false) then
    begin     
      if ValueExists(value) then
     
        //Read the value from the field
        Result := Readstring(value)       
        
      else
        ShowMessage(The value + ' does not exists under ' + theKey);
    end
    else    
      //opening the key 
        ShowMessage('Error opening key : ' + theKey);
   CloseKey;
    end;
  end;

  //for demo purposes  called by
     form1.label1.TagString := ReadReg('\SYSTEM\CurrentControlSet\Control\Nls\Locale', 'Default');

Then from the locale code held in TagString

Do the following SQL Query pseudo code as I can't get the above function to work)

CASE when TagString = 0809 then
       format  dddd/mm/yyyy 
       AS thedate
else
CASE   when TagString = (whatever the US code is) then
       format  mm/dddd/yyyy 
       AS thedate

That is my general idea.

Oh yes, the error the above code keeps throwing is 'Error opening key'. The key exists as I'm looking at in Regedit.