Partial Dates

[David Higgs]

I compile Historic Data (MySQL & DBF) with incomplete Dates, for example the Day (00/00/1950) or Month (00/06/1950) could be unknown.

I currently use the "Character" Format for storing Dates and CTOD for Sorting etc, is there an alternative?

Regards,
David

 

[GriffMG]

I would do as you are, perhaps storing in ISO style YYYYMMDD to make sorting easier, as a string - because VFP treats invalid dates (incomplete dates being invalid) as empty.

You could use a function to return the first of the month, and/or the first month of the year, for any date where you have deliberately used your nomenclature 00 to denote
an incomplete date.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

I completely agree with Griff. The Character format is really the only one that makes sense in these circumstances. And storing them as YYYYMMDD is the best option for sorting. I don't see how using functions like CTOD() is going to work, and is likely to lead to run-time errors.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Griff / Mike,

Thank you for your input. I will / have used the ISO Style Format on other applications. Just wanted to make sure there were no alternatives.

Regards,
David

 

[GriffMG]

There are always alternatives, you could use three numeric fields (one for day, month and year), but they probably wouldn't be as effective as the approach you already have.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Just wanted to make sure there were no alternatives.

It's always worth asking. You never know if someone has come up with a better solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, well, of course you could mend the dates. But as you have it, just swapping year to front and you can sort by the date. That's mentioned as the reason DTOS is the way it is, no new idea.
00/00 dates will sort before any others of the year instead of last, but that's what you got, how else to sort them? Making them 01/01 dates you would make them undistinguishable from real 01/01 dates. Any more decent strategies must base on other side knowledge you don't tell, eg like another date column noting the data entry able to fix such dates. At least for example knowing whether to sort such dates as years begin or end. Months begin or end. Any indicator, any implication.

Bye, Olaf.

 

[StefanFoerner]

Hi DPGH,

You can store all dates in exactly Your first suggested format!

The only thing You need for sorting is an own function like myDate_2_String( myDate ):

FUNCTION myDate_2_String( myDate )
    LOCAL dateString

    dateString = SUBSTR( myDate, 7, 4 );    && year
               + SUBSTR( myDate, 1, 2 );    && month
               + SUBSTR( myDate, 4, 2 )     && day

    RETURN dateString
ENDFUNC

Regards, Stefan

 

[Olaf Doschke]

That's true, of course. But why go through this conversion for any read? You could make an index with that expression, I'd not make it a function, though, as the index would then error once you use the dbf with set order and not set procedure.

Still why not store dates in the format to easily sort by and turn it to the best fit display value at display? The binary datetime type works the same way, just automatically. Rule of thumb: Data storage is done for a program, forms, reports and such are done for the user. Because of not working dates you can't make use of the native date/datetime type, but you can stay closer to it in the sense of sorting the data.

We also don't know where the data resides, the YYYYMMDD format as string works for all database, both DBFs and MySQL tables and has the sortable attribute. You can't compute with all such dates, that's for sure, if that was the main goal data cleansing would be a better choice. If you store as MM/DD/YYYY you need both a VFP and a MySQL function/expression to sort by the data in VFP cursors and in MySQL queries. Makes it less usable just for the advantage of keeping it stored as is.

You can have a computed field in VFP, eg this way:

CREATE CURSOR curDate (cDate C(8))
INSERT INTO curDate VALUES ('20170307')
SET FIELDS TO ALL
SET FIELDS TO BritsDate = SUBSTR(cDate,5,2)+'/'+RIGHT(cDate,2)+'/'+LEFT(cDate,4)
BROWSE

That has limited use this way, I would rather create a textbox taking the YYYYMMDD value as its datasource and formatting its value. Forget about using the usual controlsource mechanism, though, you can't let it set the YYYMMDD value and then display something else VFPs INPUTMASK has limited use, it can't reorder the digits, for example. One trick would be a container with two textboxes. One invisible with the field as controlsource and the other for the transformed display. The whole purpose of the second textbox is to have a second .Value decoupled from the first textbox controlsource. ProgrammaticChange events of the bound text1 are used to set the visible text2.Value to the date transformed to human readable format. That can even be editable and finally you can store back the YYYYMMDD format in text2.Valid to text1.Value and thereby edit the date in YYYYMMDD format.

Bye, Olaf.

 

[David Higgs]

Olaf / Stefan,
Thank you both for your inputs.
In transferring my Database from VFP to MySQL I have tried the various suggestions and have changed my "Date Field" from ddmmyy to yyyymmdd (Character) format. In the process I will be redesigning my Forms to incorporate the above changes.

Thanks again everyone for your help and ideas.

Regards,
David

 

[Olaf Doschke]

Minor thing: Forgive me, the MM/DD thing is not British, it's US American. So rename BritsDate to MericanDate.

Bye, Olaf.

 

[David Higgs]

Minor thing: Forgive me, the MM/DD thing is not British, it's US American. So rename BritsDate to MericanDate.

I was paying attention and did note that!

Regards,
David

 

[Olaf Doschke]

Very good!

 

[Olaf Doschke]

I have another idea to make dates like 00/00/1950 (Year) or 00/06/1950 (Month) available in date range searches:

Make two YYYMMDD begindate enddate normal date columns and in cases with original full dates fill that date in both fields, in case you only have the year, fill in YYYY0101 and YYYY1231 (converted to dates) and in case you have year and month YYYYMM01 to whatever last day of the month. Such date ranges will enable you to query where that table daterange is within a search daterange, for example: WHERE table.begindate BETWEEN beginsearchrange and endsearchrange AND table.enddate BETWEEN beginsearchrange and endsearchrange. Besides via this redefinition of the single date strings as date ranges they stand for, you can use the usual date type columns again and have all of their advantages on top of easy sorting.

Bye, Olaf.

 

[David Higgs]

Olaf,
I take on board the additional information. When filling in missing data I need to be aware of "actual dates" and dates that have been "padded" (with probably false information) to look like real dates. I think what you are suggesting takes care of that scenario. I will give it some thought.

Regards,
David

 

[Olaf Doschke]

>I need to be aware of "actual dates" and dates that have been "padded" (with probably false information) to look like real dates.
Well, staying with 00 values you don't pad them.

>I think what you are suggesting takes care of that scenario.
Somewhat. Staying with dates in string form obviously doesn't allow many operations you can do on dates and my idea is to turn all date strings into a date range. All the valid dates will have begin=end in that range. And all the dates, which are unclear, will have a range covering the unknown date. It could even be the case these invalid dates are placeholders for the month or year range, so this is no error, but intention. Just a date having day and no month would be puzzling in that interpretation.

It doesn't makes it simpler to handle, to have a date range than a simple date, but you don't have a simple date anyway. And you can simply also keep your original data string, too, to make clear from where that date range was derived from. In the end it's just an idea to move in your mind.

Bye, Olaf.

 

[David Higgs]

It doesn't makes it simpler to handle, to have a date range than a simple date, but you don't have a simple date anyway. And you can simply also keep your original data string, too, to make clear from where that date range was derived from. In the end it's just an idea to move in your mind.

Olaf,
Some very interesting ideas especially keeping the integrity of the original (sometimes incomplete) dates but having the option of placing the dates between a defined period so that the date search is more efficient.
Thank you for sharing your ideas.

Regards,
David

 

[GriffMG]

I would probably use a function in VFP to convert the date as found for 'between dates' selection so that a 00 day date always referred to the first day of the month and a 00 month date always referred to January - you would probably need a stored procedure to do something similar in SQL but in VFP it could be very simple indeed.

Function StdDate
  Parameter m.DateStr
  Private m.DateStr.m.MyDate,m.MonthStr,m.DayStr,m.YearStr
  ** Passed date in m.DateStr is in YYYYMMDD format, and date is set to British format
  m.DayStr = Right(m.DateStr,2)
  m.MonthStr = Substr(m.DateStr,5,2)
  m.YearStr = Left(m.DateStr,4)
  m.MyDate = Ctod(iif(m.DayStr<>"00",m.DayStr,"01")+"/"+iif(m.MonthStr<>"00",m.MonthStr,"01")+"/"+m.YearStr)

Return(m.MyDate)

Thus :

StdDate("20170700") would return a date for 01 July 2017
StdDate("20170000") would return a date for 01 January 2017
StdDate("20170723") would return my birthday


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

>having the option of placing the dates between a defined period

Well, yes and no.

I suggest you have these MySQL fields and populate them this way

[pre]cDateString, dBeginDate, dEnddate
'20170700' , 2017-07-01, 2017-07-31
'20170000' , 2017-01-01, 2017-12-31
'20170723' , 2017-07-23, 2017-07-23[/pre]

In regard of sorting it now depends whether you sort by cDateString or one of the real date fields. And yes, that means you can find these quirky dates in date range searches better than via the wrong string date, eg searching in the range of the whole year 2017, unless you let that be a stringsearch starting with 20170000 instead of 20170101, this year string would not be found, but dBegindate would be found.

Griffs idea basicly just would convert to the dBegindate.

With both dBegindate and dEnddate you in general would sort in such 00 dates at begin or end of month or year, but for even larger ranges you want to query, say accumulating all data for a decade, these data ranges would not be left outside. So I was actually initially giving an example of a query with a range of dates to query and checking whether each record date range is within the query range, a range in range check.

As said, it makes querying not simpler. But it gives several ways of doing so.

Another example would be you actually want to query data of one sepcific date only, then you can decide to leave out any of these smeared undefined dates, but you could also consider making your query check the specific query date to be in each records range. For the records with a 1 day only range, this is exactly what you'd expect, but it woul also include all data assigned to the same month or year, if it's record date range is including the query date. That would be a day in range check, invere of what you normally do when querying all data for a range, you query all ranges covering a certain date.

Whether you want to include or exclude such data from queries has to be decided case by case. You just have the opportunity to do as you like when having data this way, it's up to you and your fantasy and capabilites to make use of the three values. Sorting by dBegindate would actually almost sort identical as by string, sorting by dEnddate would sort in all the unclear dates to the end of the range they belong to.

All in all this way of converting these dates doesn't set rules, but converts to the best known range of date values and what you do with that info in queries and sorting data then is up to you, but you have more options than with just the string date.

Bye, Olaf.

 

[David Higgs]

Thanks guys, you've really broadened my horizon! A lot of good ideas that will no doubt help me find the most suitable solution.

Regards,
David