Monday, March 22, 2010

Calculate Date Different in seconds for far apart dates

datediff() returns an integer. The max value for an integer is 2,147,483,647. That work out to be about 24855 days

select 2147483647 / 60 / 60 / 24
 
Result = 24855

This is fine for most cases but if you have bad data, you will get overflow error.


I have written a function just to calculate the different of 2 dates in terms of seconds. It returns the seconds in a bigint.


It first find the different in terms of days and then multiply by 24 x 60 x 60 to convert to number of seconds. Then it subtract and add the time different for the start date and the end date to get the total different in seconds for the 2 dates.


Here is the function.



create function fn_diffsecond
(
    @date1 datetime, 
    @date2 datetime
)
returns bigint
as
begin
    return         (convert(bigint, datediff(day, @date1, @date2)) * 24 * 60 * 60)
        -     (datediff(second, dateadd(day, datediff(day, 0, @date1), 0), @date1))
        +     (datediff(second, dateadd(day, datediff(day, 0, @date2), 0), @date2))
end

Wednesday, March 10, 2010

Calculating EAN 8 / EAN 13 Check Digit

Just wrote a quick function to calculate the check digit for EAN 8 / EAN 13

 


CREATE  FUNCTION sfn_ean_chkdigit
(    
    @barcode    varchar(20)
)
RETURNS CHAR(1)
AS
BEGIN
    DECLARE
        @chk_digit    int,
        @chk        int
 
    DECLARE    @num TABLE
    (
        num    int
    )
 
    IF    LEN(@barcode) NOT IN (7, 12)
    BEGIN
        RETURN     NULL
    END
 
    INSERT INTO @num 
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  SELECT  5 UNION ALL SELECT  6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
 
    SELECT    @chk_digit = SUM(CONVERT(int, SUBSTRING(@barcode, LEN(@barcode) - num + 1, 1)) * CASE WHEN num % 2 = 1 THEN 3 ELSE 1 END)
    FROM    @num
    WHERE    num    <= LEN(@barcode)
 
    SELECT    @chk_digit = (10 - (@chk_digit % 10)) % 10
 
     RETURN  CHAR(ASCII('0') + @chk_digit)
END