CODEWITHSUNDEEP
mysqlexceldatenumbers
hocuspocus31
hocuspocus31

Reputation: 183

Convert Date to a number in mysql (Like how a date converts in Excel)

What is the correct function to use when i want to convert a date to a number. Like when you enter a date in excel for example (now()) and then it will show the date but when you click on the number format it will show you a number.

I tried to use Unix timestamp but its not exactly the output i was looking for.

The date i entered is today's date =now()

and the output i was hoping to get is

42146

what's the correct function to get this result in mysql?

Thank you

Upvotes: 1

Views: 3362

Answers (1)

spencer7593
spencer7593

Reputation: 108400

Microsoft Excel bases date serial numbers from Jan 1, 1904 or from Jan 1, 1900 (depends on the setting in the workbook.)

To generate a date "serial number" similar to what Excel uses, just calculate the number of days between NOW() (or whatever date you want to convert), and the base date. For example:

     SELECT DATEDIFF(NOW(),'1900-01-01') AS excel_serial_date_1900

(You might need to add 1 or subtract 1 to get the exact same value that Excel uses, I've not tested that.) If your Excel workbook is using the 1904 based serial date, use '1904-01-01' as the base date in the expression.

Note: the DATEDIFF function returns integer number of days, operates only on the "date" portion, and doesn't include any fraction of a day.

Reference: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

Upvotes: 1

Related Questions