Topic: SQLQuery with localized date format

Does anyone knows a trick to display the result of SQL query including a date to be displayed in the localized date format the same way it does when using the auto display in a grid?

I already know how to force a date format with strftime with Sql but it becomes strange when the app displays a mix of date formats depending on how the grid has been designed, whether with autogrid or with an Sql query button/auto search.

This is important to me as I want the app to be international so I wonder what is used within MVD to display the date in local date format in the autogrids.

Re: SQLQuery with localized date format

tcoton wrote:

Does anyone knows a trick to display the result of SQL query including a date to be displayed in the localized date format the same way it does when using the auto display in a grid?

I already know how to force a date format with strftime with Sql but it becomes strange when the app displays a mix of date formats depending on how the grid has been designed, whether with autogrid or with an Sql query button/auto search.

This is important to me as I want the app to be international so I wonder what is used within MVD to display the date in local date format in the autogrids.

Attach Screenshot

3 (edited by tcoton 2023-09-18 19:50:41)

Re: 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.

Post's attachments

Attachment icon Date Time Formats.png 122.96 kb, 66 downloads since 2023-09-18 

Re: SQLQuery with localized date format

DateTimeToStr()
DateToStr()
TimeToStr()
FormatDateTime()
SQLDateTimeToDateTime()
strftme()
and working with registry

Re: SQLQuery with localized date format

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.

Re: SQLQuery with localized date format

If you are interested in what's inside, take a book on Delphi.
But if you do it from what’s available in the program, that’s completely different.
I hope nothing prevents you from determining the date and time format on your computer ...
And the query in the script can be modified  ...

7 (edited by CDB 2023-09-19 09:34:20)

Re: SQLQuery with localized date format

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.

On a clear disk you can seek forever

Re: SQLQuery with localized date format

Can you clarify your idea?

I don't think this is exactly possible using SQLite.
...then select the required SQL strftime formatting function....
...As an aside I think SQL Server...

I can't put it together.

9 (edited by CDB 2023-09-20 10:53:41)

Re: SQLQuery with localized date format

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.

On a clear disk you can seek forever

10 (edited by sparrow 2023-09-20 12:31:44)

Re: SQLQuery with localized date format

Here I'm querying the value of the first day of the week.

 
var
  reg: TRegistry;
begin
 reg := TRegistry.Create;
  reg.Access := KEY_READ;
  reg.RootKey := HKEY_CURRENT_USER;
  reg.OpenKey('Control Panel\International',false);
  frstDay := strtoint(reg.ReadString('iFirstDayOfWeek'));                    // Get first week day ( 6 - sunday, 0 - monday...)
  reg.Free;
end;

I would like to note that this registry section already contains the information you need, not just the locale code.
Separators, short date time format, long date time format, etc.


P.S. If you have a time and date format on the computer where the program is running, you can generate strftime()
and substitute it into your queries in the script.   '%Y-%m-%d'    '%Y/%m/%d'     '%d/%m/%Y'   '%d-%m-%Y' ... As you will need
For large output volumes in a "tablegrid" this will be the most optimal way, in my opinion. For single output in "Edit"
you can use the combination DateToStr(SQLDateTimeToDateTime()) .

Perhaps you will go a different way, your own.

11

Re: SQLQuery with localized date format

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

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

On a clear disk you can seek forever

Re: SQLQuery with localized date format

I am pretty sure Dmitry uses a function to display the date with the local format but no one knows how it works except for Dmitry.
Anyway, I have been playing around with the registry keys, I can get and use the values of short date and time format but I must transform the returned value to work with SqLite queries format!!

So far, I got this but I need to re-format the registry key output to match "%d" for day, "%m" for month and "%Y" for year as the registry spits out the system format for the short date which is "dd" for day, "mm" for month and "yyyy" for year with whatever separator you have set. Is there an easy way or do I have to write a whole lot of CASE...?

begin
  reg := TRegistry.Create;
  reg.Access := KEY_READ;
  reg.RootKey := HKEY_CURRENT_USER;
  reg.OpenKey('Control Panel\International',false);
  shDate := (reg.ReadString('sShortDate'));                    // Get short date format but must be set to %d/%m/%Y or whatever order/separator for the date
  lgTime := (reg.ReadString('sTimeFormat'));                   //Get Time format but must be set to %H:%M:%S
  reg.Free;
  form1.tablegrid1.dbSQL:= 'select strftime("'+shDate+'", date) as "Date" from date'; //returns only the registry key value instead of formatted date
  Form1.TableGrid1.dbSQLExecute;

end;

13

Re: SQLQuery with localized date format

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.

On a clear disk you can seek forever

14 (edited by sparrow 2023-10-17 12:39:35)

Re: SQLQuery with localized date format

The simplest option.

Post's attachments

Attachment icon dat-cot.zip 326.77 kb, 131 downloads since 2023-10-17 

15 (edited by sparrow 2023-10-17 19:21:09)

Re: SQLQuery with localized date format

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

Post's attachments

Attachment icon date-ext.zip 327.72 kb, 127 downloads since 2023-10-17 

Re: SQLQuery with localized date format

sparrow wrote:

The simplest option.

Thanks a lot Sparrow, the simplest option does not work for the US format and probably for other countries using the M/d/yyyy as a date format.

Re: SQLQuery with localized date format

sparrow wrote:

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.

Re: SQLQuery with localized date format

Yes it is possible.  All in your hands. 
One procedure and one function.

19 (edited by tcoton 2023-10-19 20:37:12)

Re: SQLQuery with localized date format

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.

Re: SQLQuery with localized date format

tcoton wrote:

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 SQL 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.



No comments ...

Post's attachments

Attachment icon date-ext-funct.zip 328.19 kb, 127 downloads since 2023-10-19 

Re: SQLQuery with localized date format

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...

22 (edited by sparrow 2023-10-20 07:11:26)

Re: SQLQuery with localized date format

tcoton wrote:

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...


Button
Description
The component is used to perform actions when the user clicks on it.
Class: TdbButton

Properties
Property     Type     Description
dbSQL    String    It makes sense if the button has an "SQL query" or "Report (SQL)" action assigned to it. To access the SQL query.

https://myvisualdatabase.com/help_en/script_button.html

Re: SQLQuery with localized date format

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

Re: SQLQuery with localized date format

tcoton wrote:

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


At the end of this post, Dmitry talks about stored procedures in MYSQL.
The MYSQL stored procedure can also be called using the button with the "SQL Query" function.
Unfortunately, MVD cannot create a stored function.

Re: SQLQuery with localized date format

My bad, I was confused by the use of "procedure" instead of "stored procedure".