BS2AD Converter MySQL
DELIMITER //
-- Helper function to get days in a specific Nepali month
CREATE FUNCTION GetNepaliMonthDays(year INT, month INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result INT;
CASE year
WHEN 1975 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 1976 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30);
WHEN 1977 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 1978 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 1979 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 1980 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 1981 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 1982 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30);
WHEN 1983 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 1984 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 1985 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 1986 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30);
WHEN 1987 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 1988 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 1989 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 1990 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31);
WHEN 1991 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 1992 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 1993 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 1994 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 1995 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 1996 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 29, 30, 30, 29, 30, 30);
WHEN 1997 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 1998 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 1999 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2000 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2001 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2002 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2003 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2004 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2005 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2006 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2007 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2008 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31);
WHEN 2009 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2010 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2011 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2012 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30);
WHEN 2013 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2014 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2015 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2016 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30);
WHEN 2017 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2018 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2019 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2020 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2021 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2022 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30);
WHEN 2023 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2024 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2025 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2026 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2027 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2028 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2029 THEN SET result = ELT(month, 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30);
WHEN 2030 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2031 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2032 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2033 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2034 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2035 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31);
WHEN 2036 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2037 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2038 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2039 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30);
WHEN 2040 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2041 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2042 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2043 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30);
WHEN 2044 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2045 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2046 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2047 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2048 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2049 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30);
WHEN 2050 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2051 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2052 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2053 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30);
WHEN 2054 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2055 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2056 THEN SET result = ELT(month, 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30);
WHEN 2057 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2058 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2059 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2060 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2061 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2062 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 31, 29, 30, 29, 30, 29, 31);
WHEN 2063 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2064 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2065 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2066 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31);
WHEN 2067 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2068 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2069 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2070 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30);
WHEN 2071 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2072 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2073 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31);
WHEN 2074 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2075 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2076 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30);
WHEN 2077 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2078 THEN SET result = ELT(month, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2079 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2080 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30);
WHEN 2081 THEN SET result = ELT(month, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31);
WHEN 2082 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30);
WHEN 2083 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30);
WHEN 2084 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30);
WHEN 2085 THEN SET result = ELT(month, 31, 32, 31, 32, 30, 31, 30, 30, 29, 30, 30, 30);
WHEN 2086 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30);
WHEN 2087 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30);
WHEN 2088 THEN SET result = ELT(month, 30, 31, 32, 32, 30, 31, 30, 30, 29, 30, 30, 30);
WHEN 2089 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30);
WHEN 2090 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30);
WHEN 2091 THEN SET result = ELT(month, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30);
WHEN 2092 THEN SET result = ELT(month, 30, 31, 32, 32, 31, 30, 30, 30, 29, 30, 30, 30);
WHEN 2093 THEN SET result = ELT(month, 30, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30);
WHEN 2094 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30);
WHEN 2095 THEN SET result = ELT(month, 31, 31, 32, 31, 31, 31, 30, 29, 30, 30, 30, 30);
ELSE SET result = 0;
END CASE;
RETURN result;
END //
-- Helper function to sum Nepali month days
CREATE FUNCTION SumNepaliMonthDays(year INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
WHILE i <= 12 DO
SET total = total + GetNepaliMonthDays(year, i);
SET i = i + 1;
END WHILE;
RETURN total;
END //
-- Helper function to validate Nepali date
CREATE FUNCTION ValidateNepaliDate(year INT, month INT, day INT)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
DECLARE max_days INT;
IF year < 1975 OR year > 2095 THEN
RETURN FALSE;
END IF;
IF month < 1 OR month > 12 THEN
RETURN FALSE;
END IF;
SET max_days = GetNepaliMonthDays(year, month);
IF day < 1 OR day > max_days THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END //
-- Main function to convert Nepali (BS) date to English (AD) date
CREATE FUNCTION BS2AD(ne_date VARCHAR(10))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE ne_year INT DEFAULT 0;
DECLARE ne_month INT DEFAULT 0;
DECLARE ne_day INT DEFAULT 0;
DECLARE date_start DATE;
DECLARE daycount INT DEFAULT 0;
DECLARE i INT;
DECLARE j INT;
DECLARE english_date DATE;
DECLARE year_str VARCHAR(4);
DECLARE month_str VARCHAR(2);
DECLARE day_str VARCHAR(2);
DECLARE temp_date VARCHAR(10);
-- Parse the date string
SET temp_date = TRIM(ne_date);
-- Check format (must be YYYY-MM-DD)
IF temp_date NOT REGEXP '^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$' THEN
RETURN 'Invalid date format. Please use YYYY-MM-DD';
END IF;
-- Extract parts
SET year_str = SUBSTRING_INDEX(temp_date, '-', 1);
SET temp_date = SUBSTRING(temp_date, LENGTH(year_str) + 2);
SET month_str = SUBSTRING_INDEX(temp_date, '-', 1);
SET day_str = SUBSTRING(temp_date, LENGTH(month_str) + 2);
SET ne_year = CAST(year_str AS SIGNED);
SET ne_month = CAST(month_str AS SIGNED);
SET ne_day = CAST(day_str AS SIGNED);
-- Basic validation
IF ne_month < 1 OR ne_month > 12 THEN
RETURN 'Invalid month. Month must be between 1 and 12';
END IF;
IF ne_day < 1 OR ne_day > 31 THEN
RETURN 'Invalid day. Day must be between 1 and 31';
END IF;
-- Validate Nepali date
IF NOT ValidateNepaliDate(ne_year, ne_month, ne_day) THEN
RETURN 'Invalid Nepali date';
END IF;
SET date_start = '1918-04-13';
SET daycount = 0;
-- Add days from start year to target year
SET i = 1975;
WHILE i < ne_year DO
SET daycount = daycount + SumNepaliMonthDays(i);
SET i = i + 1;
END WHILE;
-- Add days from months before target month
SET j = 1;
WHILE j < ne_month DO
SET daycount = daycount + GetNepaliMonthDays(ne_year, j);
SET j = j + 1;
END WHILE;
-- Add remaining days
SET daycount = daycount + ne_day - 1;
-- Calculate English date
SET english_date = DATE_ADD(date_start, INTERVAL daycount DAY);
RETURN DATE_FORMAT(english_date, '%Y-%m-%d');
END //
DELIMITER ;
-- Example usage:
-- SELECT BS2AD('2080-01-01');
-- SELECT BS2AD('2079-12-30');
-- SELECT BS2AD('2000-01-01');
