Date formatting

[tanc]

I've got a spreadsheet that someones asked me to turn into a database but the formatting is a bit messed up. the date system used is YYYY/MM/DD, although this is not always followed its easy enough to fix, the problem comes when some of the dates are 1940/05/00, as the days are not always known. is there a way to put in blank days other than changing the format to text? Thanks for any help

 

[Brianwarnock]

If it is a date field then it has to be a valid date, I assume the current field is not a date field if it accepts a day 00.

The best approach depends on the application , the fact that you have days of 00 suggests that the exact date is not important, I would convert all of the 00 to 01 and retain a date datatype rather than go to text as text is less flexible for handling dates, especially sorting and searching.

Brian

 

[tanc]

thanks the issue with assigning a 01 instead of a 00 would be confusion if there was an event on 01/01/1940 for example, the only other way I could think off would be to separate the date into 3 separate columns with null data but sorting and querying then becomes an issue

 

[Brianwarnock]

You could create a field indicating that the year and month were known but not the day for records where you did this. You would then be able to omit these records or not as required by the question being asked, as I said earlier if the day is not known it cannot be important , or at least it has to be a compromise.

Text nor multiple fields do not alter the issue, only make working with the data more difficult.

Brian