0

I have inherited a MySQL database which (for historical reasons) contains a field which stores a date as number of days since 1900-01-01. I want to be able to select a date_format() version of this date but can't figure a way of doing so.
Is there a way of manipulating the contents of the column such that I can pass the manipulated value to date_format() so it is recognised as a valid date?
I realise there is a solution to this which involves doing one query, then manipulating the field (in PHP) then passing back in as a secondary query - but I need this to be a single query solution.

Improve this question

2 Answers 2

Reset to default
1

Is the MySql FROM_DAYS() function what you're looking for - see here for docs?

However, depending on your database design and use case, you might consider adding a column with the date (result of FROM_DAYS()), so that you can do queries against that column. When you have to apply a function to a column before being able to use this in a query, it should ring warning bells that these queries could be quite inefficient to run.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks - just what I needed. The efficiency is not too important in this case as long as it can be done in one query.
1

Can you create views and functions on the database? If so I would create a function with the query you are currently using to convert the time and then create a view using that function to display the time as a timestamp. This will improve your efficiency and make your php code cleaner :)

Improve this answer

1 Comment

Can't create views or functions unfortunately

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.