DateTime data format handling

Try something like:

QueryString = SUBSTITUTE ("Preselect each TableX where TableX.CreateDateTime > &1 ", 
                                          QUOTER (DateTimeVar)) . 

Thanks Mike this seems to work.

QueryString = SUBSTITUTE ("Preselect each TableX where TableX.CreateDateTime > &1 ", QUOTER (DateTimeVar)) .

I found a few other ways:

QueryString = "Preselect each TableX where TableX.CreateDateTime > " + QUOTER (DateTimeVar). // Pretty much the same as Mike's solution

or more complicated:

QueryString = "Preselect each TableX where TableX.CreateDateTime > datetime(" + string(today) + "," + string(mTime - 10000) + ")".

I'm curious about one thing.

I tried doing a simple for each from a character screen and it didn't work, it gave me an 'Incompatible data types' when I do the CreateDateTime = QUOTER(DateTimeVar).

I get why this does not work, I don't get why it works in the query-prepare statement!

I know some of you will say "It works, just be happy!!!" but hey it's in my nature to want to understand things so I'm only half-happy!!

Thanks again.

Olivier.

I'm curious about one thing.
I tried doing a simple for each from a character screen and it didn't work, it gave me an 'Incompatible data types' when I do the CreateDateTime = QUOTER(DateTimeVar).
I get why this does not work, I don't get why it works in the query-prepare statement!

CreateDateTime is defined as DATETIME and QueryString as (probably) CHARACTER. The QUOTER function returns CHARACTER, and so this is a simple typing (as in data types) error.

-- peter

or more complicated:

That cries for SUBSTITUTE then ....

I get why this does not work, I don't get why it works in the query-prepare statement!

Applications that build query strings at runtime with the STRING function will likely give a run time error or, perhaps even worse, simply give wrong results with non-default session format settings.

I know some of you will say "It works, just be happy!!!" but hey it's

in my nature to want to understand things so I'm only half-happy!!

Well, it was a very important question...   I have seen too much code with dynamic queries built without quotes around non-character expressions.

I guess in case of date-time fields the quotes are also required because of the space characters in the default format as in:

23/07/2009 17:23:13,568+02:00

I guess in case of date-time fields the quotes are also required because of the space characters in the default format as in:
23/07/2009 17:23:13,568+02:00

Yes, but that's almost a blessing, since you are forced to fix it. With dates and decimals the parser and compiler may not always protest, but could still return wrong data.

Here's a simple test that illustrates the problem with decimals. In this case the problem only happens when the criteria has decimals. A search value of 200 would not cause any problems.  With dates you are more likely to get a compiler warning, but a date like for example 01/04/12 would compile in all date-formats, but give very different results depending on the setting.

define temp-table test
   field decvalue as decimal.
define variable searchvalue as decimal no-undo.   
define variable querystring as character no-undo.   
   
create test.
decvalue = 200.
create test.
decvalue = 20000.

searchvalue = 199.99.
querystring = "preselect each test where decvalue > " + string(searchvalue). 

run runquery("Non quoted american value",querystring).

/* european will go wrong if query has decimals - query parser assumes american - query engine uses session settings */
session:numeric-format="european".
run runquery("Non quoted american value - compiles, but evaluates as european", querystring).

querystring = "preselect each test where decvalue > " + string(searchvalue). 
run runquery("Not quoted european value", querystring).

/* quotes around the expression makes the query parser evaluate the expression according to session settings */
querystring = "preselect each test where decvalue > " + quoter(searchvalue). 
run runquery("quoted",querystring).

/* works also with american  of course */
session:numeric-format="american".
querystring = "preselect each test where decvalue > " + quoter(searchvalue). 
run runquery("quoted",querystring).

/**************************/
procedure runquery:
  define input  parameter msgtitle as character no-undo.
  define input  parameter querystring as character no-undo.
  
  define variable hquery as handle no-undo.   
  define variable msg as char no-undo.   
  create query hquery.
  msgtitle = session:numeric-format + " - " + msgtitle.
  do on error undo, throw: 
      hquery:add-buffer(buffer test:handle).
      hQuery:query-prepare(querystring).
      hquery:query-open().   
      message  msg skip
           "Query:" querystring skip(1)
           "Num records: " hquery:num-results 
          view-as alert-box title msgtitle.    
      catch  e as Progress.Lang.Error :
         message "Query:" querystring skip(1) skip
                  e:GetMessage(1)
         view-as alert-box error title msgtitle .
      end catch.
  end.
end.

Thanks Havard.

I tried your code and saw exactly what you meant. It's nice to know how the query parser works.

Olivier

A small correction:

> The query parser expects date-format to be “dmy” and a numeric-format to be “American”.

That should be: '.. expects the date-format to be "mdy"...'

And while I am at it, I should issue a word of warning about the practice of quoting these expressions. Remember that if you cross the AppServer boundary, the session settings of the client might well be different from the server. Therefore, do not send those values accross as characters when they originate on the client. Instead, send them over in their native data types and quote them on the server.

rbf wrote:
A small correction:
> The query parser expects date-format to be “dmy” and a numeric-format to be “American”.
That should be: '.. expects the date-format to be "mdy"...'

oops... thanks.

And while I am at it, I should issue a word of warning about the practice of quoting these expressions. Remember that if you cross the AppServer boundary, the session settings of the client might well be different from the server. Therefore, do not send those values accross as characters when they originate on the client. Instead, send them over in their native data types and quote them on the server.

Also an important reminder. The quoting and the querying must be done with the same settings. This can also be achieved by ensuring that the server has the same settings as the client as we do in Dynamics.