xHarbour's DateTime data types

One of the most valuable features of our loved CA-Clipper is, no doubt, its ability to work with date data types, using this feature you can add or substract days to a given date, or even get the difference in days between two dates with a single substract operation.

In those days this feature was really appreciated and it's still in our days.

But times change, and today we can use xHarbour along with ADO (Activex Data Objects) and using both we can have access to a different SQL based databases, this comes with some "issues" regarding data handling that may be, by reading this article, I can help you to solve.

Most of the SQL tables support fields with the typical Xbase data types (character, numeric, logical, date and memo), but also they support other data types such float, integer, char, string, and the reason to publish this article: DateTime data types.

A DateTime field stores exactly that, a field with two elements merged: the date and the time.

xHarbour provides of a full support to DateTime data types, we must be a little bit careful using them, but the truth is that to understand the concept is very easy.

Sintax:

A DateTime requires a special format which is:

{^ YYYY/MM/DD HH:MM:SS.CCC [AM|PM] }

The information goes between {} signs and after the first { comes a ^ symbol.

In these data types, the date ALWAYS comes first, the format must always be: YEAR-MONTH-DAY, the year always must be indicated with 4 digits, 2 more digits are used for the month and 2 for the day, the separator always is "/" (slash) no other separator can be used, the format of the date in the DateTime variable is always the same, regardless the format stablished using SET DATE or SET EPOCH, these "sets" commands only affect the DATE data types.

After the date, comes the time, a blank space is used as a separator between them; the time format is HOUR-MINUTE-SECOND-MILISECOND, seconds can be expressed also with 3 digits decimals for the miliseconds.

Time data can be used in 12 and 24 hours formats adding AM or PM, if you use the 24 hours format, then the time can be expressed like: 13:50 and no PM is required, otherwise, if you use the 12 hours format you must indicate AM for morning times and PM for afternoon times: 1:50 PM.

Now let's see the "curiosities" about this kind of data type:

You can remove the date part or the time part when writting a DateTime, and then, default values are used to "complete" the missing parts, if you remove the date, xHarbour will use 1899/12/30 as the default date, this is not a random given value, where does it come from ?, it's defined by Microsoft as the starting date for the OCX/ActiveX controls (which means that this kind of controls cannot older dates).

If you remove the time, the defaul value is 00:00:00: midnight of the given date.

DateTime values work as date ones, you can add days, hours, minutes and seconds, and also, you can get the difference of days, hours, minutes, etc between two DateTime values.

To use DateTime data types in a data table, the field has to be defined as "T" type (supported by the most of the SQL based databases and Advantage Database Server), the value can be stored directly into the field without a previous convert operation. For example, if you are using ADO to handle a SQL table with xHarbour you can use something like this:

oRs:Fields("dtetme"):Value := {^ 2008/01/24 21:30}

As well as there's a DATE() function, which returns the current computer's date, and a function TIME() which returns the current time, xHarbour has the function DATETIME() that does exactly what you are thinking: returns the computer's date and time as a DateTime data type, so you can do things like these:

dtToday := DATETIME()

Remember Clipper's CTOD() and DTOC() functions, used to convert from Character to Date and Date to Character ?, well in xHarbour we have something similar: CTOT() (Character to DateTime) and TOTC() (DateTime to Character):

dtDate := CTOT("2008/01/24 01:50 PM")
cDateTime := TOTC(DATETIME()) // returns: "2008/01/24 21:30"

What comes now may be helpful for you:

One of the greatest headaches of the Clipper Programmer is that they don't know the difference between DTOC() and DTOS() functions, so they have problems when indexing DBF files using a date field as the index key, same can happen to DateTime data.

DTOC() returns a date as a charater WITH THE FORMAT GIVEN IN SET DATE COMMAND, this means that:

SET DATE BRITISH
cDate1 := DTOC(DATE())
? cDate1 // "24/01/08"

SET DATE FRENCH
cDate2 := DTOC(DATE())
? cDate2 // "24-01-08"

SET DATE ANSI
cDate3 := DTOC(DATE())
? cDate3 // "08:01:24"

SET DATE AMERICAN
cDate4 := DTOC(DATE())
? cDate4 // "01/24/08"

You always get a formated character string acording to the SET DATE given, this is not a valid index key because if you forget to SET the DATE to the right format in your application, or other program uses the same data table and doesn't have the right SET DATE, you will have difficults to find the records in the table, getting wrong results.

The right way to create an index under a date field is using the DTOS() function, which returns a character string with the date IN SAME TABLE'S STORED FORMAT.

SET DATE BRITISH
cDate1 := DTOS(DATE())
?cDate1 // "20080124"

SET DATE ANSI
cDate2 := DTOS(DATE())
?cDate2 // "20080124"

SET DATE FORMAT AA-DD-MM
cDate3 := DTOS(DATE())
?cDate3 // "20080124"

No, it's not a mistake, DTOS always returns the date in the format YYYYMMDD, this is the format used to store dates in a DBF table, and that's why the Date fields are 8 positions long, because 4 digits are used for the year (century included), 2 for the month, and 2 more for the day, separators ARE NOT STORED (sorry if you thought other thing), separators are given by the SET DATE command.

Back to the DateTime world, xHarbour has similar functions: TTOS() and STOT()

cDT := TTOS(DATETIME()) // returns: "20080124212900"
dtDateTim := STOT("20080417231530") // returns {^ 2008/04/17 23:15:30}

And finally some interesting samples of operations between DateTime values, taken from xHarbour manual (a must to have, buy your copy today !).


PROCEDURE Main
LOCAL d1, d2, nDiff

SET CENTURY ON
SET TIME FORMAT TO "hh:mm:ss.ccc"

? DateTime() // returns: {^ 2008/01/24 21:37:55}
? {ˆ 2007/04/26} // returns: 04/26/2007
? {ˆ 05:30:12.345} // returns: 12/30/1899 05:30:12.345
? {ˆ 05:30:12.345 PM} // returns: 12/30/1999 17:30:12.345

** Empty values

? d1 := {ˆ 0/0/0 } // returns: / /
? Empty( d ) // returns: .T.

** Operations

? d1 := {ˆ 2007/04/26 18:30:00 } //returns: 04/26/2007 18:30:00.000
? d2 := StoD("20070426") // returns: 04/26/2007
? nDiff := d1-d2, "days" // returns: 0.77 días
? TString( nDiff*86400 ) // returns: 18:30:00

** Adding 2 days to a DateTime
? d1 + 2 // returns: 04/28/2007 18:30:00.000

** Adding 2 hours to aDateTime
? d1 + 2/24 // returns: 04/26/2007 20:30:00.000

** Adding 2 minutes to a DateTime
? d1 + 2/(24*60) // returns: 04/26/2007 18:32:00.000

** Adding 2 seconds to a DateTime
? d1 + 2/(24*3600) // returns: 04/26/2007 18:30:02.000

RETURN

Before you start asking, the answer is YES: ALL this stuff can be used in xHarbour console mode, Xailer and MiniGUI, in FiveWin only works if you are using xHarbour, not Harbour (DateTime are not supported yet, as far as I know).