Tricky Date Problem!

Hi, Damon -

Thanks for your support.

All of the dates are already in the database. There are over 1,500 of them. New dates are added via a proper date gizmo - so will be in the proper format.

I need to work with what I've got if possible.

:0/

Perch

Using the MySQL Console that is included in the Developer Console plugin, what does that date format look like for dates without days?
http://www.interactivetools.com/add-ons/developer-console/

A date entering using the date dropdowns or date picker looks like this:
2016-03-29 12:02:49

Depending on what the date format looks like for dates without the days entered, you may be able to do a search and replace with a MySQL statement to add the day.

Let me know what the date format in the database looks like for dates without days entered.

Hi, Damon –

The dates in the DB have been entered as simple strings:

• 04/67
• 10/71
• 09/73
• 13/04/82
• Etc…

Perch

Hi,

If you leave the simple strings dates as is and then enter records with new dates using the date picker or dropdown date field, the formats will be different and you will not be able to sort on them.

You need to convert dates in this format:

04/67
10/71
09/73
13/04/82

to be in a date format like this:

1967-04-01 00:00:00
1971-10-01 00:00:00
1973-09-01 00:00:00
1982-04-13 00:00:00

Then when the dates are all in the same format you can sort them and output them in the format needed.

How to do this is the question. You could export the table with the dates to CSV and then work on them in a plain text/code editor.

After the changes, save and import the data back into CMS Builder.

Will this work for you?

Hi, Damon -

I had thought of that approach but it's not really a solution.

As I said in my original post, for ordering and filtering it would be ok to infer that a person died on the first of the month. However, if I actually change the date then I am confirming that as a fact - which I cannot do.

What I was hoping to find was a way of pulling the dates out of the DB and tricking the ordering system into treating this 04/1967 as if it was actually 01/04/1967.

This would enable me to order the entire list like this:

23/03/1967

29/03/1967

04/1967

02/04/1967

12/04/1967

05/1967

03/05/1967

Etc.

:0/

Perch

Hi,

If you changed the date format to be like this YYYY/MM/DD, you could use the sorting in CMS Builder and the frontend:

1967/03/27
1967/04
1967/04/02
1967/05

This would also work with dates that don't have a day entered. Maybe moving forward instead of having a date picker which will give you another format, you could just have a text field with instructions after it to use this format: YYYY/MM/DD

You can also limit the allowed characters to be numbers and forward slash.

Sorry, I'm running out ideas. 

Thanks. I appreciate your help.

:0)