SQL date format

[Amedeo]

With executesql script, date Is in format MM-DD-YYYY... How May I convert It to DD/MM/YYYY?

[comment]

1 minute ago, Amedeo said:

With executesql script, date Is in format MM-DD-YYYY.

Not sure what you're referring to. If you're using the ExecuteSQL() function, then you will get dates in YYYY-MM-DD format. If you want another format, you can either reformat the date in the SQL query itself or post-process the SQL result. There is also an (undocumented) option to get the dates "as entered". Which option is best for you depends on what exactly you're doing and for what purpose.

 

[Amedeo]

ook. How may I reformat the date in the SQL query as you said?

[comment]

Try:

ExecuteSQL ( 
"SELECT RIGHT(STRVAL(YourDatefield), 2) + '/' + SUBSTR(STRVAL(YourDatefield), 6, 2) + '/' + LEFT(STRVAL(YourDatefield), 4)
FROM YourTable" ; 
"" ; "" )

 

[rivet]

there is the trick of concatenating an empty string before the field;

ExecuteSQL ( "SELECT dte FROM ...
//returns 2022-10-20

ExecuteSQL ( "SELECT ''||dte FROM ...
//returns 10/20/2022  (how it was entered)

 

Edited March 14, 2023 by rivet