I got this wild idea to fix a date format issue i've been having.

The idea is to use column info to act as a template for the date format since mm/dd/yy can get really confusing for the database since I always use YYYY-mm-dd but my users like to mix things around.

The thing is that I have some trouble to figure out exactly how the SQL would look like.

My idea is something like this:

Tablename: USER
Col1, Col2, Col3, date_template

And the other info:

Tablename: activity
item, quantity, price, date, time

Yes, I know that it could be fixed in the code of the page but since I'm not the creator of the site I won't go into fiddle around with the code until I got some time to make a new page.


I think you will have an easier time to simply cast whatever the user throws at you to a date format that you want, to keep it all uniform. E.g. in PHP like this: date('Y-m-d', strtotime($userdate))

It can also be done in sql with convert(date, '28/09/2016'), however I think you need to specify the format. Link

  • The database only saves their date as 0000-00-00 right now since they don't use the right format, otherwise I'd use some nifty sql-convert-solution. One problem is that the users upload their csv:s with the same structure as the activity table and this results in the same issue, a date filled with zeros. – F.Dahlberg Sep 28 '16 at 8:15
  • How are the users entering the data? Because I think if you can't convert the data to a format you want, how can you guess the format they are writing the dates in? Perhaps you should be looking for a more strict way for the users to enter data so you get it as you want. – Philip Sep 28 '16 at 8:41
  • The data is imported from an CSV-file and that is where the problem occurs. As for now, the page and software they use saves the records to a csv and then they have to upload it them selves to the database. Don't ask me how they where thinking when they started to develop this system because it was long before my time at this place. I'd rather see a direct upload from the page and software or at least use a correct datetime or timestamp. – F.Dahlberg Sep 28 '16 at 10:10
  • Okay, that doesn't sound so good. In your case I think it would be best to write a script that runs through the data and tries to match the dates with regular expressions as best as you can and then simply convert it to a common format. The script can be server-side on the upload. I think the idea you had originally with the extra column is not so good, because again you will be guessing the format and adding extra data. For example European vs American on the date 2008/05/08. I hope it helps, because I can't really think of another solution right now. – Philip Sep 28 '16 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.