Need to display date and time in "MM/DD/YYYY hh:mm:ss" format
- Thread starter Mark123
- Start date
TomBascom
Curmudgeon
If you were using an up to date and supported version of Progress you would probably have (or want) a single DateTime field.
To display two fields in the desired format:
Note: the 'format "x(8)"' is optional -- the default for a character element is "x(8)". But explicitly specifying it makes the code clearer.
If what you really want to do is to create a string holding that result then you would want something along these lines:
Note #2 -- mm/dd/yyyy vs dd/mm/yyyy is subject to the -d startup parameter or SESSION
ATE-FORMAT.
To display two fields in the desired format:
Code:
display
dateField format "99/99/9999"
string( timeField, "hh:mm:ss" ) format "x(8)"
.Note: the 'format "x(8)"' is optional -- the default for a character element is "x(8)". But explicitly specifying it makes the code clearer.
If what you really want to do is to create a string holding that result then you would want something along these lines:
Code:
newString = string( dateField, "99/99/9999" ) + " " + string( timeField, "hh:mm:ss" ).Note #2 -- mm/dd/yyyy vs dd/mm/yyyy is subject to the -d startup parameter or SESSION
ATE-FORMAT.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.
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.
TomBascom
Curmudgeon
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.
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.
GregTomkins
Active Member
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).
TomBascom
Curmudgeon
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:
Than it is to code:
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.
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.