Thanks. I also have another question: How can I to put data-time into where clause , something like this:
For each mytable where (create_date + create_time) > "05/29/2009 14:22:24"
display
fileld1
filed2
string(create_date, "99/99/9999" ) + " " + string(create_time, "hh:mm:ss")
skip.
end.
There is no built in way to combine the fields as you have attempted in your imaginary example.
This is the major reason why you want to be running a version that supports the DateTime data type. Queries on date and time that use two fields are a royal PITA.
Your choices are:
1) Carefully think through the logic using a complex WHERE clause with multiple fields. Good luck. Even if you get it right someone will probably screw it up doing maintenance while you're on vacation
2) Rethink the schema design and add a DateTime surrogate field. There are several ways to do this. A decimal field where the date is to the left of the decimal and the time to the right is one approach. Another is an integer field with appropriate shifting or an offset from some epoch. Or you could create a composite character field. There are pros and cons to each approach.
3) Upgrade to OE10 and use a native DateTime field.
With respect, I would say a 'complex where clause' (2 fields?) is less prone to error than trickery such as embedding multiple fields into a single decimal field with the decimal as a magic separator. (I remember seeing how Dynamics did this for some purpose, which solidified my non-interest in Dynamics, something I have never looked back on... but that's another topic).
Creating your own DateTime datatype isn't difficult and people have been doing it for a long time. The main reason to prefer a built-in type is inter-operability.
Personally I don't much like the decimal thing -- I use the UNIX style type; number of seconds since 1/1/1970.
It is much easier and vastly more reliable to correctly code:
Code:
for each order no-lock where
orderDT >= dateTime( 6, 1, 2009, 12, 0, 0 ) and
orderDT < dateTime( 6, 1, 2009, 13, 0, 0 ):
/* do something with orders placed betweeen noon and 1pm on 6/1/2009... */
end.
Than it is to code:
Code:
for each order no-lock where
order.orderDate = date( 6, 1, 2009 ) and
order.orderTime >= 43200 and order.orderTime < 46800:
/* do something... */
end.
But to really appreciate what a PITA 2 fields are code a WHERE clause that spans a range such as noon on June 1st to 8am on June 3rd.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.