Sta: with Dates

[Doug Easterbrook]

hi das:

This approach is similar to what Paul Mulroney indicates in his post.    and then the topic went down the path of ISO standards, which misses the point.



while not strictly required in postgres, we add the DATE or TIMESTAMP verbs in front of dates, and do not put any reliance on #FD.     #FD, #FDT, #FT are all for managing visual display of dates in the interface, and are not suited internal usage of dates, especially when talking to databases.

eg, with english and french in canada, the customary date displays are very different - and none of them correspond to the format of what postgres expects.  This, its better to be explicit on the date format in a STA: block.



Finally, many of our dates are timestamps to the 1/100th of a second, so using #FD limits precision of saving those timestamps.


our typical sta: statement woulk look like

begin statement
sta: update table
sta: set myDate=[tdatabase.$sqldate($cinst.myDate)],
sta: set myTimeStamp=[tdatabase.$sqldate($cinst.myTimeStamp)]
sta: where
…..
end if


the key to understanding this is

$cinst means that you are inside the table class referring to the same variable as you might get with @[myDate] .    if you are doing this sql outside a table class, you can change the $cinst to  theList you are processing.

second is the use of ‘tDatabase’.    it is just an object that contains functions.   in our case, it is a bunch of database helper functions.


here is $sqlDate(pValue)


Calculate queryValue as dat(pValue,'y-M-D')
Calculate queryValue as con("DATE '",queryValue,"'")
Quit method queryValue


here is $sqlTimestamp(pValue)

Calculate queryValue as dat(pValue,'y-M-D H:N:S.s')
Calculate queryValue as con("TIMESTAMP '",queryValue,"'")
If len(pTimeZone)
Calculate queryValue as con('(',queryValue,' ',pTimeZone,')')
End If
Quit method queryValue




a STA: block only returns text that can be send to postgres (or whatever database engine you are using).

using functions like do two things:

— help you get the format of the field type tight
— if using mutliple database backends, you can format the dates, times, binary fields and what have you specific to that database/SQL standard.








you have to be explicit in framing how the date and timestamp looks.   we do the same for binary objects, to add in escape characters

$sqlBinary(pValue)

If isclear(pValue)
Quit method 'null'
Else
Quit method con("E'\\x",bintohex(pValue),"'")
End If


and character — where we escape certain characters.  like the ‘\'

$sqlChar(pValue)

Do tStringFields.$replace(pValue,"\","\\",pValue) ## Replace single \ with \\ so it is properly escaped
Calculate queryValue as con("'",pValue,"'")
Quit method queryValue


Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Jul 21, 2022, at 11:24 AM, ADJob <mats at adjob.se> wrote:
> 
> Hi,
> 
>> We are not so fortunate as to live in such a logical country!
> 
> :-)
> 
> You can try to cast as date as well.  https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/106
> 
> /Mats
> 
> 
> 
> 
> 
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com