Talk:Date Data Type

From CometWiki

Posted by Grant Foraker on September 14, 2009 at 14:43:13:

Adding a Date data type to Comet has been discussed at several past Dealer/Developer meetings. So, as a baby step, can we add a "D" field type to #CFILES.
.
Right now the majority of dates in my files are either "YYYYMMDD" or "MM/DD/YYYY". The "YYYYMMDD" ones for date sorting (in keys mostly) and the "MM/DD/YYYY" ones for display.
The Date Serial Number is another possible option.
.
So, in the Reporter
 .
SELECTING IF TRANS.DATE EQ "%FromDate%"
.
would prompt a Date Picker because TRANS.DATE was a "D" instead of a "S" field.

Posted by Jon Sacks on September 15, 2009 at 06:28:44:

In Reply to: Date data type posted by Grant Foraker on September 14, 2009 at 14:43:13:
.
All dates should be stored in the system as serial date/time and the mask applied indicates the way it should be displayed.

I agree with the last bit just not sure about this serial date/time format badge 19:24, 14 October 2009 (UTC)

Posted by Steve Auerbach on September 15, 2009 at 07:41:34:

 In Reply to: Re: Date data type posted by Jon Sacks on September 15, 2009 at 06:28:44:
.
Jon, of course, is right. Dates should be stored as serial date/time. Sadly the Q machine did not know about such sophistication. 
#CFILES has this legacy thing to deal with.
And, of course, I agree with Grant BUT we need more than a D type. We need type codes like those so mnemonically defined for DATE2NUM.
Easy to remember; flows trippingly from the tongue. Sadly we need more than one date code.

Posted by Jim Guerber on September 15, 2009 at 11:04:38:

In Reply to: Re: Date data type posted by Steve Auerbach on September 15, 2009 at 07:41:34:
.
Of course you are all right. I totally agree that Comet should know about dates.
That is why we introduced the comet Julian date feature in the date2num() functions etc.
The thing we lack is some sort of syntax that the program could use to tell Comet what to do.
.
I suggest you start a wiki page that could describe such a syntax.
Once the syntax is nailed down, we could start to build something.
As a start, look at the windows way of dealing with dates. Don't forget time as well.

Posted by Bob A on September 16, 2009 at 07:34:53:

In Reply to: Re: Date data type posted by Jim Guerber on September 15, 2009 at 11:04:38:
.
"As a start, look at the windows way of dealing with dates. Don't forget time as well."
.
I think there are two "Windows" ways....
.
1) Windows the operating system, which is milliseconds since 00h00m00s GMT on 1970-01-01 which includes both date and time in one number
2) Windows Office which is days since 1899-10-30 followed by a fraction representing percent of a day (24 hours)
.
since there is a long standing bug in the second date (see: http://support.microsoft.com/kb/214326 ),
I would surely propose keeping with the COMET serial number of days as the base for elapsed days.
.
Although I will not profit from it, as I have COMET16, the COMET32 limit on the size of the numbers would easily allow the first option (milliseconds)
since we have access to this number easily thru NUM2DATE (Option #6)

Posted by steve auerbach on September 15, 2009 at 13:18:23:

In Reply to: Re: Date data type posted by Jim Guerber on September 15, 2009 at 11:04:38: .
.
I think you're going further than Jon or I envision.   
What do you mean by "the program?" The Reporter?
All I want to do is to tell BB that this date is formated as mmddyy or yymmdd or yyyymmdd.
Then she can use Date2Num to deal with it properly.
I'd even settle for just putting the MTB type code in #CFILES so all she'd have to do is generate the right kind of Date2Num in the selecting statement.
What do you have in mind??

Posted by Jim Guerber on September 15, 2009 at 16:35:08:

In Reply to: Re: Date data type posted by steve auerbach on September 15, 2009 at 13:18:23:
.
I was thinking of the whole IB language.
When you say "Tell Barbara", you could tell her over the phone, but it wouldn't matter.
Barbara handles both the IB compiler and the Report Generator. I am now guessing you are talking about the report generator only.
.
After all Barbara is Comet Woman!, she can do anything.

Posted by Grant Foraker on October 01, 2009 at 10:11:06:

The Date Data Type thing sort of fizzled out. I wanted something in DbMGR and #CFILES. For now. The date sequence number has some advantages but I prefer human readable dates.
.
So, how about a third option in the "F or S option". "D" for date and a new date format field in #CFILES that describes the format. 
.
0 - date sequence
1 - YYYYMMDD
2 - MM/DD/YYYY
3 - DD/MM/YYYY for the rest of the world. badge 07:28, 8 October 2009 (UTC)
etc.
. 
This should be enough for the Reporter and CMAINT to extract any date in any of the approved formats and prompt a Date Picker dialog.

Serial Date Time - Why badge 07:28, 8 October 2009 (UTC)

I can see no good reason for storing dqtes in the Serial date time format. It would appear to me that the only reason for storing it this way is to do arithmetic on the dates.
The majority of dates in all my systems over 40 years of programming are for information only, occasionally the month becomes critical ie calculating interest.
The system can calculate the serial date time format if required, so why not store them in YYYYMMDD + Time format, if someone wants to do arithmetic then calculate the other format as required.
Is Serial date time format similar to Julian date ?
If not, why is it needed at all, Julian is fine for most arithmetic date operations.
I agree with Grant, we want and need human readable dates, why have the Maint program if we don't use it.

Data vs. Information

What I am looking for is that the date as it is stored on the system is completly transparent to the date that is displayed to the user.

If the date is in the format of yyyymmddhhmmssmm vs. 318542.1070 is a question of using legacy programs like MAINT to look at data, for going forward lets call this "CometDate". I see the value in being able to eyball data and see a more human readable value. The idea I had in mind was for Comet to better know how to display the information, this leave the following choices:

1. Have the application program manipulate the date (Enter programs, Procs, etc.)

2. Have the OS (Comet) return the data into a string in the requested format

  FORMAT C1AFormat: Cnbr$;CustomerName$;OpenDate$,3;
  Where ,3 indicated to Comet to place the data in the string format as (DD/MM/YY).
  But when the data is written it allways goes down as the "CometDate".

3. Leverage Date2Num/Num2Date as long as both functions are fully implemented.

The main thing that I am looking for is making it easier to manipulate dates. I want to make CancelDate on an order = OrderDate + 7. I don't want to have to first do conversions, no other system that I work in does not allow me to simply say CancelDate = OrderDate+7. That is the end of the syntax, so what do we need to do to make it that easy?