MySQL Function to Calculate Excel-style Dates

, , Leave a comment

drop function if exists exceldate;

delimiter //

-- Function that returns an Excel-style or Delphi-style date value
-- from a MySQL date. A date value of 0 represents 1899-12-30.</em>
create function exceldate(p_date date)
    returns int
    sql security invoker
begin
    return to_days(p_date) - 693959;
end //

delimiter ;

select '1899-12-31', exceldate('1899-12-31') -- should be 1
union
select '2011-10-18', exceldate('2011-10-18'); -- should be 40834
 

Leave a Reply