It All Makes Sense Now

The blog of Mats Gefvert.

MySQL Function to Calculate Excel-style Dates

Posted 10/18/2011 in Software Development

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
    return to_days(p_date) - 693959;
end //

delimiter ;

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

Luhn (mod 10) Check Digit Algorithm in MySQL

Posted 10/13/2011 in Software Development

drop function if exists luhn;
drop function if exists luhn_check;

delimiter //

-- Function that calculates a Luhn (mod 10) check digit from a numeric string.
-- The behavior is undefined if the string contains anything else than digits.
-- Assumes that the string does not have a check digit added yet, so it starts
-- with a weight of 2 at the last digit.
create function luhn(p_number varchar(31))
 returns char(1)
 sql security invoker
 declare i, mysum, r, weight int;

 set weight = 2;
 set mysum = 0;
 set i = length(p_number);

 while i > 0 do
 set r = substring(p_number, i, 1) * weight;
 set mysum...