SQLQuery with localized date format (Page 1 of 2)

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.

That seems a neater way to do it than my idea Sparrow.

Perhaps TCoton can let us know if that works for his project.

Tcoton,

While I would personally read in the country code and then use CASE statements as I wrote above, I think you could do it another way.

First count the length of the string shDate for max_index.

Within a for loop, use dtFormat:= Pos(shDate, ' ')

Then use the loop index, as the position to

Insert(dtFormat, '/', loopIndex);

And perform a similar iteration for the time.

Advanced mode for dates.
Supported (d, dd, M, MM, yy, yyyy in different combinations + different separators)
Something like this.

Works great, I wonder if we could write a function and keep the manipulated date format in a global variable as I have dates in multiple tables with different field names, to avoid writing the whole block for each and every procedure in a quite big project.

What if I have multiple date columns with different names? Is there as simpler way of setting a date format for any date/datetime column to be used within a SQLite query or do I have to write numerous procedures for each and every column having a date.

Up to now, I do have 8 columns in different tables containing a date: Release_date, Purchase_date, Return_date, etc... using Sparrow's code is limited to one column named "dat". Is there a trick ? I am calling on all advanced programers here, I already spent hours on StackOverflow and most people are limited to one date field.

Thanks Sparrow, I learned a lot!!

Does anyone knows if I can call a function from within a "SQL Query"- Button or do I have to call all my button SQL queries from the script tab? I tried and I get a SQL error...

I was referring to this post where Dmitry was saying that you can call a procedure using a button with SQL Query: https://myvisualdatabase.com/forum/view … hp?id=5481