Need to display date and time in "MM/DD/YYYY hh:mm:ss" format

[Mark123]

We have 2 separate fields update_date which is like 05/29/09 and update_time like 64,430.
How can I display them like 05/29/2009 17:53:50 ?

Thanks.

 

[TomBascom]

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:

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:

  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 SESSIONATE-FORMAT.

 

[gnome]

You may also be interested with 24 hour format:

display string(timeField,'HH:MM:SS AM').

cool.

 

[Mark123]

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.

 

[TomBascom]

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.

 

[gnome]

I would prefer the first option. I think it is much more safe and reliable.

 

[gnome]

I would prefer the first option. I think it is much more safe, reliable and easy.

 

[TomBascom]

After you've tried it for a while you won't think so.

 

[GregTomkins]

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]

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:

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:

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.