CODEWITHSUNDEEP
sql-server
gaurav bhatt
gaurav bhatt

Reputation: 23

How do i convert English date to Nepali date

How do i convert English date 2017/11/11 to its Nepali date(date according to Nepali calendar) .I have searched lot of article but couldn't found any article based on that.Please help

Upvotes: 0

Views: 8764

Answers (4)

Gyaneshwor Gaud
Gyaneshwor Gaud

Reputation: 11

    MySQL Function :

        DELIMITER $$

CREATE FUNCTION `ad2bs`(`ad` VARCHAR(10)) RETURNS varchar(10) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE year INT;
    DECLARE month INT;
    DECLARE day INT;
    
    DECLARE bs varchar(10);
    
    SET @cumulative_sum = DATEDIFF(ad, '1943-04-14');
   
    SELECT 
        BS_Year, 
        IF((BS_Month + 1) > 12, (BS_Month + 1) % 12, BS_Month + 1), 
        running_total + 1 
    INTO 
        year, month, day 
    FROM (
        SELECT *, 
            @cumulative_sum := @cumulative_sum - no_of_days AS running_total 
        FROM datelog
        ORDER BY SNo
    ) AS subquery 
    WHERE running_total <= no_of_days 
    ORDER BY SNo ASC 
    LIMIT 1;
   
    RETURN CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'));
    
END$$

DELIMITER ;


CREATE TABLE datelog (
  SNo int(8) NOT NULL,
  BS_Year int(11) DEFAULT NULL,
  BS_Month int(11) DEFAULT NULL,
  no_of_days varchar(255) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO datelog (SNo, BS_Year, BS_Month, no_of_days) VALUES
(1, 2000, 1, '30'),
(2, 2000, 2, '32'),
(3, 2000, 3, '31'),
(4, 2000, 4, '32'),
(5, 2000, 5, '31'),
(6, 2000, 6, '30'),
(7, 2000, 7, '30'),
(8, 2000, 8, '30'),
(9, 2000, 9, '29'),
(10, 2000, 10, '30'),
(11, 2000, 11, '29'),
(12, 2000, 12, '31'),
(13, 2001, 1, '31'),
(14, 2001, 2, '31'),
(15, 2001, 3, '32'),
(16, 2001, 4, '31'),
(17, 2001, 5, '31'),
(18, 2001, 6, '31'),
(19, 2001, 7, '30'),
(20, 2001, 8, '29'),
(21, 2001, 9, '30'), .. so on

Upvotes: 0

Simanta Tiwari
Simanta Tiwari

Reputation: 1

Here for the exact solution i have created date library first and stored it in a table:

Step 1

Create Table to store Date Library

CREATE TABLE [dbo].[DateData]
(
[Year] [float] NULL,
[Baishakh] [float] NULL,
[Jestha] [float] NULL,
[Ashad] [float] NULL,
[Shrawan] [float] NULL,
[Bhadra] [float] NULL,
[Ashwin] [float] NULL,
[Kartik] [float] NULL,
[Mangshir] [float] NULL,
[Poush] [float] NULL,
[Magh] [float] NULL,
[Falgun] [float] NULL,
[Chaitra] [float] NULL
)

Step 2

Now insert the given data in table


 INSERT INTO dbo.DateData
    (
        Year,
        Baishakh,
        Jestha,
        Ashad,
        Shrawan,
        Bhadra,
        Ashwin,
        Kartik,
        Mangshir,
        Poush,
        Magh,
        Falgun,
        Chaitra
    )
         Select '1975'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1976'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '1977'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '1978'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1979'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1980'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '1981'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1982'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1983'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1984'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '1985'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1986'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1987'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1988'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '1989'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1990'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1991'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1992'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '1993'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1994'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1995'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1996'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '1997'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1998'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '1999'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2000'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2001'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2002'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2003'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2004'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2005'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2006'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2007'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2008'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2009'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2010'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2011'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2012'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2013'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2014'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2015'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2016'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2017'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2018'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2019'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2020'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2021'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2022'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2023'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2024'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2025'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2026'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2027'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2028'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2029'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'32'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2030'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2031'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2032'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2033'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2034'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2035'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2036'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2037'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2038'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2039'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2040'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2041'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2042'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2043'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2044'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2045'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2046'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2047'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2048'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2049'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2050'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2051'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2052'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2053'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2054'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2055'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2056'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'32'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2057'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2058'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2059'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2060'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2061'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2062'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2063'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2064'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2065'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2066'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2067'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2068'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2069'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2070'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2071'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2072'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2073'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'31'Chaitra  Union All 
        Select '2074'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2075'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2076'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2077'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2078'Year,'31'Baishakh,'31'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2079'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2080'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2081'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2082'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2083'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2084'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2085'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'30'Bhadra,'31'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2086'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2087'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2088'Year,'30'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'30'Bhadra,'31'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2089'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2090'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2091'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2092'Year,'30'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2093'Year,'30'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2094'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2095'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2096'Year,'30'Baishakh,'31'Jestha,'32'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'29'mangshir,'30'Poush,'29'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2097'Year,'31'Baishakh,'32'Jestha,'31'Ashad,'32'Shrawan,'31'Bhadra,'30'Ashwin,'30'Kartik,'30'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra  Union All 
        Select '2098'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'29'Kartik,'30'mangshir,'29'Poush,'30'Magh,'29'Falgun,'31'Chaitra  Union All 
        Select '2099'Year,'31'Baishakh,'31'Jestha,'32'Ashad,'31'Shrawan,'31'Bhadra,'31'Ashwin,'30'Kartik,'29'mangshir,'29'Poush,'30'Magh,'30'Falgun,'30'Chaitra

Step 3

Create function to convert AD to BS


CREATE FUNCTION [dbo].[fn_AD_BS_Conversion] ( @EnglishDate DATE,@separator VARCHAR(250) )
    RETURNS VARCHAR(50)
    AS
        BEGIN
            DECLARE 
                @DayDiff INT ,
                @Result VARCHAR(50),
                --@EnglishDate DATE,
                @YearSn BIGINT=1,
                @YearMaxSn BIGINT,
                @Baishakh BIGINT,
                @Jestha BIGINT,
                @Ashad BIGINT,
                @Shrawan BIGINT,
                @Bhadra BIGINT,
                @Ashwin BIGINT,
                @Kartik BIGINT,
                @Mangshir BIGINT,
                @Poush BIGINT,
                @Magh BIGINT,
                @Falgun BIGINT,
                @Chaitra BIGINT,
                @Year BIGINT,
    ---- declaration of static date
                @staticenglishdate DATE='1918-04-13',
                @staticnepaliyear int='1975',
                @staticnepalimonth VARCHAR(250)='01',
                @staticnepaliday int='01',
                @month VARCHAR(250),
                @day VARCHAR(250),
                @remainingDays BIGINT
     SELECT @separator=ISNULL(@separator,'-')
    DECLARE  @Dates TABLE
    (
    RowNo BIGINT,
    [Year] [float] NULL,
    [Baishakh] [float] NULL,
    [Jestha] [float] NULL,
    [Ashad] [float] NULL,
    [Shrawan] [float] NULL,
    [Bhadra] [float] NULL,
    [Ashwin] [float] NULL,
    [Kartik] [float] NULL,
    [Mangshir] [float] NULL,
    [Poush] [float] NULL,
    [Magh] [float] NULL,
    [Falgun] [float] NULL,
    [Chaitra] [float] NULL
    ) 
    SELECT @DayDiff = DATEDIFF(DAY,@staticenglishdate,@EnglishDate)+1
    SELECT @remainingDays=@DayDiff
    INSERT INTO @Dates
    (
        RowNo,
        Year,
        Baishakh,
        Jestha,
        Ashad,
        Shrawan,
        Bhadra,
        Ashwin,
        Kartik,
        Mangshir,
        Poush,
        Magh,
        Falgun,
        Chaitra
    )
    SELECT ROW_NUMBER() OVER (ORDER BY DD.Year) RowNo, DD.Year,
DD.Baishakh,DD.Jestha,DD.Ashad,DD.Shrawan,DD.Bhadra,DD.Ashwin,DD.Kartik,DD.MangshirDD.Poush,DD.Magh,DD.Falgun,DD.Chaitra FROM dbo.DateData AS DD WHERE DD.Year>=@staticnepaliyear ORDER BY DD.Year ASC
    SELECT @YearMaxSn=MAX(D.RowNo) FROM @Dates AS D
    WHILE @DayDiff>0
    BEGIN
    IF @remainingDays>0
    BEGIN
    SELECT @Baishakh=D.Baishakh,@Jestha=D.Jestha,@Ashad=D.Ashad,@Shrawan=D.Shrawan,@Bhadra=D.Bhadra,@Ashwin=D.Ashwin,@Kartik=D.Kartik,@Mangshir=D.Mangshir,@Poush=D.Poush,@Magh=D.Magh,
    @Falgun=D.Falgun,@Chaitra=D.Chaitra,@Year=D.Year
    FROM @Dates AS D WHERE D.RowNo=@YearSn
    END

    IF @DayDiff>=0 
    BEGIN 
        IF (@DayDiff-@Baishakh<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='01'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Baishakh
        END
        SELECT @remainingDays-=@Baishakh
        IF (@DayDiff-@Jestha<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='02'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Jestha
        END
        SELECT @remainingDays-=@Jestha
        IF (@DayDiff-@Ashad<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='03'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Ashad
        END
        SELECT @remainingDays-=@Ashad
        IF (@DayDiff-@Shrawan<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='04'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Shrawan
        END
        SELECT @remainingDays-=@Shrawan
        IF (@DayDiff-@Bhadra<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='05'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Bhadra
        END
        SELECT @remainingDays-=@Bhadra
        IF (@DayDiff-@Ashwin<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='06'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Ashwin
        END
        SELECT @remainingDays-=@Ashwin
        IF (@DayDiff-@Kartik<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='07'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Kartik
        END
        SELECT @remainingDays-=@Kartik
        IF (@DayDiff-@Mangshir<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='08'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Mangshir
        END
        SELECT @remainingDays-=@Mangshir
        IF (@DayDiff-@Poush<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='09'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Poush
        END
        SELECT @remainingDays-=@Poush
        IF (@DayDiff-@Magh<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='10'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Magh
        END
        SELECT @remainingDays-=@Magh
        IF (@DayDiff-@Falgun<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='11'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Falgun
        END
        SELECT @remainingDays-=@Falgun

        IF (@DayDiff-@Chaitra<=0 AND @remainingDays>0)
        BEGIN
        SELECT @day=@DayDiff,@month='12'
        END
        ELSE
        BEGIN
        SELECT @DayDiff-=@Chaitra
        END
        SELECT @remainingDays-=@Chaitra
    END
    ELSE
    BEGIN
    SELECT @day = CASE WHEN LEN(@day)<2 THEN '0'+@day ELSE @day END
    SELECT @Result=CAST(@Year AS VARCHAR(250))+@separator+@month+@separator+@day
    BREAK
    END
    IF @DayDiff<=0
    BEGIN
    SELECT @day = CASE WHEN LEN(@day)<2 THEN '0'+@day ELSE @day END
    SELECT @Result=CAST(@Year AS VARCHAR(250))+@separator+@month+@separator+@day
    BREAK
    END
    SELECT @YearSn+=1
    END
    RETURN @Result
        END;

Step 4

Execution

Now Execute the created function giving the suitable separator. It returns BS date in YYYY @Saperator MM @Saperator DD.
if you execute 
SELECT dbo.fn_AD_BS_Conversion('1945-04-12','/')

then it returns

2001/12/30

Upvotes: 0

Sameer
Sameer

Reputation: 391

English Date: Anno Domini (AD)  and Nepali Date: Bikram Sambat (BS) Date Converter. Converting AD to BS is not a rocket science, all it depends on data mapping. Here, I have created Table DateLog which holds English Date as well as Nepali Date. Conversion Logic reside in this scalar function [dbo].[ufn_GetBikramSambatDate].

/****** Object:  Table [dbo].[DateLog]    Script Date: 5/4/2018 4:47:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DateLog](
    [SNo] [int] IDENTITY(1,1) NOT NULL,
    [BS_Year] [int] NULL,
    [BS_Month] [tinyint] NULL,
    [EnglishDate] [date] NULL,
PRIMARY KEY CLUSTERED 
(
    [SNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[DateLog] ON 

INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (1, 2073, 4, CAST(N'2016-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (2, 2073, 5, CAST(N'2016-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (3, 2073, 6, CAST(N'2016-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (4, 2073, 7, CAST(N'2016-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (5, 2073, 8, CAST(N'2016-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (6, 2073, 9, CAST(N'2016-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (7, 2073, 10, CAST(N'2017-01-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (8, 2073, 11, CAST(N'2017-02-12' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (9, 2073, 12, CAST(N'2017-03-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (10, 2074, 1, CAST(N'2017-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (11, 2074, 2, CAST(N'2017-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (12, 2074, 3, CAST(N'2017-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (13, 2074, 4, CAST(N'2017-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (14, 2074, 5, CAST(N'2017-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (15, 2074, 6, CAST(N'2017-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (16, 2074, 7, CAST(N'2017-10-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (17, 2074, 8, CAST(N'2017-11-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (18, 2074, 9, CAST(N'2017-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (19, 2074, 10, CAST(N'2018-01-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (20, 2074, 11, CAST(N'2018-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (21, 2074, 12, CAST(N'2018-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (22, 2075, 1, CAST(N'2018-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (23, 2075, 2, CAST(N'2018-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (24, 2075, 3, CAST(N'2018-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (25, 2075, 4, CAST(N'2018-07-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (26, 2075, 5, CAST(N'2018-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (27, 2075, 6, CAST(N'2018-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (28, 2075, 7, CAST(N'2018-10-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (29, 2075, 8, CAST(N'2018-11-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (30, 2075, 9, CAST(N'2018-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (31, 2075, 10, CAST(N'2019-01-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (32, 2075, 11, CAST(N'2019-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (33, 2075, 12, CAST(N'2019-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (34, 2076, 1, CAST(N'2019-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (35, 2076, 2, CAST(N'2019-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (36, 2076, 3, CAST(N'2019-06-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (37, 2076, 4, CAST(N'2019-07-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (38, 2076, 5, CAST(N'2019-08-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (39, 2076, 6, CAST(N'2019-09-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (40, 2076, 7, CAST(N'2019-10-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (41, 2076, 8, CAST(N'2019-11-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (42, 2076, 9, CAST(N'2019-12-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (43, 2076, 10, CAST(N'2020-01-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (44, 2076, 11, CAST(N'2020-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (45, 2076, 12, CAST(N'2020-03-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (46, 2077, 1, CAST(N'2020-04-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (47, 2077, 2, CAST(N'2020-05-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (48, 2077, 3, CAST(N'2020-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (49, 2077, 4, CAST(N'2020-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (50, 2077, 5, CAST(N'2020-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (51, 2077, 6, CAST(N'2020-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (52, 2077, 7, CAST(N'2020-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (53, 2077, 8, CAST(N'2020-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (54, 2077, 9, CAST(N'2020-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (55, 2077, 10, CAST(N'2021-01-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (56, 2077, 11, CAST(N'2021-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (57, 2077, 12, CAST(N'2021-03-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (58, 2078, 1, CAST(N'2021-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (59, 2078, 2, CAST(N'2021-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (60, 2078, 3, CAST(N'2021-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (61, 2078, 4, CAST(N'2021-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (62, 2078, 5, CAST(N'2021-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (63, 2078, 6, CAST(N'2021-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (64, 2078, 7, CAST(N'2021-10-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (65, 2078, 8, CAST(N'2021-11-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (66, 2078, 9, CAST(N'2021-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (67, 2078, 10, CAST(N'2022-01-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (68, 2078, 11, CAST(N'2022-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (69, 2078, 12, CAST(N'2022-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (70, 2079, 1, CAST(N'2022-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (71, 2079, 2, CAST(N'2022-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (72, 2079, 3, CAST(N'2022-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (73, 2079, 4, CAST(N'2022-07-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (74, 2079, 5, CAST(N'2022-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (75, 2079, 6, CAST(N'2022-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (76, 2079, 7, CAST(N'2022-10-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (77, 2079, 8, CAST(N'2022-11-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (78, 2079, 9, CAST(N'2022-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (79, 2079, 10, CAST(N'2023-01-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (80, 2079, 11, CAST(N'2023-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (81, 2079, 12, CAST(N'2023-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (82, 2080, 1, CAST(N'2023-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (83, 2080, 2, CAST(N'2023-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (84, 2080, 3, CAST(N'2023-06-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (85, 2080, 4, CAST(N'2023-07-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (86, 2080, 5, CAST(N'2023-08-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (87, 2080, 6, CAST(N'2023-09-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (88, 2080, 7, CAST(N'2023-10-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (89, 2080, 8, CAST(N'2023-11-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (90, 2080, 9, CAST(N'2023-12-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (91, 2080, 10, CAST(N'2024-01-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (92, 2080, 11, CAST(N'2024-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (93, 2080, 12, CAST(N'2024-03-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (94, 2081, 1, CAST(N'2024-04-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (95, 2081, 2, CAST(N'2024-05-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (96, 2081, 3, CAST(N'2024-06-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (97, 2081, 4, CAST(N'2024-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (98, 2081, 5, CAST(N'2024-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (99, 2081, 6, CAST(N'2024-09-17' AS Date))
GO
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (100, 2081, 7, CAST(N'2024-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (101, 2081, 8, CAST(N'2024-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (102, 2081, 9, CAST(N'2024-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (103, 2081, 10, CAST(N'2025-01-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (104, 2081, 11, CAST(N'2025-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (105, 2081, 12, CAST(N'2025-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (106, 2082, 1, CAST(N'2025-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (107, 2082, 2, CAST(N'2025-05-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (108, 2082, 3, CAST(N'2025-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (109, 2082, 4, CAST(N'2025-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (110, 2082, 5, CAST(N'2025-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (111, 2082, 6, CAST(N'2025-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (112, 2082, 7, CAST(N'2025-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (113, 2082, 8, CAST(N'2025-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (114, 2082, 9, CAST(N'2025-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (115, 2082, 10, CAST(N'2026-01-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (116, 2082, 11, CAST(N'2026-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (117, 2082, 12, CAST(N'2026-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (118, 2083, 1, CAST(N'2026-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (119, 2083, 2, CAST(N'2026-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (120, 2083, 3, CAST(N'2026-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (121, 2083, 4, CAST(N'2026-07-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (122, 2083, 5, CAST(N'2026-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (123, 2083, 6, CAST(N'2026-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (124, 2083, 7, CAST(N'2026-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (125, 2083, 8, CAST(N'2026-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (126, 2083, 9, CAST(N'2026-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (127, 2083, 10, CAST(N'2027-01-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (128, 2083, 11, CAST(N'2027-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (129, 2083, 12, CAST(N'2027-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (130, 2084, 1, CAST(N'2027-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (131, 2084, 2, CAST(N'2027-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (132, 2084, 3, CAST(N'2027-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (133, 2084, 4, CAST(N'2027-07-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (134, 2084, 5, CAST(N'2027-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (135, 2084, 6, CAST(N'2027-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (136, 2084, 7, CAST(N'2027-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (137, 2084, 8, CAST(N'2027-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (138, 2084, 9, CAST(N'2027-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (139, 2084, 10, CAST(N'2028-01-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (140, 2084, 11, CAST(N'2028-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (141, 2084, 12, CAST(N'2028-03-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (142, 2085, 1, CAST(N'2028-04-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (143, 2085, 2, CAST(N'2028-05-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (144, 2085, 3, CAST(N'2028-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (145, 2085, 4, CAST(N'2028-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (146, 2085, 5, CAST(N'2028-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (147, 2085, 6, CAST(N'2028-09-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (148, 2085, 7, CAST(N'2028-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (149, 2085, 8, CAST(N'2028-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (150, 2085, 9, CAST(N'2028-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (151, 2085, 10, CAST(N'2029-01-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (152, 2085, 11, CAST(N'2029-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (153, 2085, 12, CAST(N'2029-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (154, 2086, 1, CAST(N'2029-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (155, 2086, 2, CAST(N'2029-05-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (156, 2086, 3, CAST(N'2029-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (157, 2086, 4, CAST(N'2029-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (158, 2086, 5, CAST(N'2029-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (159, 2086, 6, CAST(N'2029-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (160, 2086, 7, CAST(N'2029-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (161, 2086, 8, CAST(N'2029-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (162, 2086, 9, CAST(N'2029-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (163, 2086, 10, CAST(N'2030-01-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (164, 2086, 11, CAST(N'2030-02-13' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (165, 2086, 12, CAST(N'2030-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (166, 2087, 1, CAST(N'2030-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (167, 2087, 2, CAST(N'2030-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (168, 2087, 3, CAST(N'2030-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (169, 2087, 4, CAST(N'2030-07-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (170, 2087, 5, CAST(N'2030-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (171, 2087, 6, CAST(N'2030-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (172, 2087, 7, CAST(N'2030-10-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (173, 2087, 8, CAST(N'2030-11-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (174, 2087, 9, CAST(N'2030-12-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (175, 2087, 10, CAST(N'2031-01-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (176, 2087, 11, CAST(N'2031-02-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (177, 2087, 12, CAST(N'2031-03-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (178, 2088, 1, CAST(N'2031-04-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (179, 2088, 2, CAST(N'2031-05-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (180, 2088, 3, CAST(N'2031-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (181, 2088, 4, CAST(N'2031-07-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (182, 2088, 5, CAST(N'2031-08-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (183, 2088, 6, CAST(N'2031-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (184, 2088, 7, CAST(N'2031-10-18' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (185, 2088, 8, CAST(N'2031-11-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (186, 2088, 9, CAST(N'2031-12-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (187, 2088, 10, CAST(N'2032-01-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (188, 2088, 11, CAST(N'2032-02-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (189, 2088, 12, CAST(N'2032-03-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (190, 2089, 1, CAST(N'2032-04-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (191, 2089, 2, CAST(N'2032-05-14' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (192, 2089, 3, CAST(N'2032-06-15' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (193, 2089, 4, CAST(N'2032-07-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (194, 2089, 5, CAST(N'2032-08-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (195, 2089, 6, CAST(N'2032-09-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (196, 2089, 7, CAST(N'2032-10-17' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (197, 2089, 8, CAST(N'2032-11-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (198, 2089, 9, CAST(N'2032-12-16' AS Date))
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (199, 2089, 10, CAST(N'2033-01-14' AS Date))
GO
INSERT [dbo].[DateLog] ([SNo], [BS_Year], [BS_Month], [EnglishDate]) VALUES (200, 2089, 11, CAST(N'2033-02-13' AS Date))
SET IDENTITY_INSERT [dbo].[DateLog] OFF


/****** Object:  UserDefinedFunction [dbo].[ufn_GetBikramSambatDate]    Script Date: 5/4/2018 4:47:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Sameer>
-- Create date: <Create Date, 2018-05-04,>
-- Description: <Description, ,Scalar Function to convert English date to Nepali Date (Bikram Sambat)>
-- =============================================
CREATE FUNCTION [dbo].[ufn_GetBikramSambatDate] ( @EnglishDate DATE )
RETURNS VARCHAR(50)
AS
    BEGIN
        DECLARE @BS_Year INT ,
            @BS_Month VARCHAR(2) ,
            @NpDay VARCHAR(2) ,
            @EngDay DATE ,
            @DayDiff INT ,
            @DayName INT ,
            @Result VARCHAR(50);  

        SELECT  @EngDay = EnglishDate ,
                @BS_Year = BS_Year ,
                @BS_Month = BS_Month
        FROM    dbo.DateLog (NOLOCK)
        WHERE   DATEDIFF(DAY, EnglishDate, @EnglishDate) >= 0
        ORDER BY EnglishDate;   



        SELECT  @DayDiff = DATEDIFF(DAY, @EngDay, @EnglishDate);  
        SELECT  @NpDay = 1 + @DayDiff;  


        SET @NpDay = REPLICATE('0', 2 - LEN(@NpDay)) + @NpDay;
        SET @BS_Month = REPLICATE('0', 2 - LEN(@BS_Month)) + @BS_Month;

        SET @Result = CONVERT(VARCHAR(4), @BS_Year) + '-'
            + CONVERT(VARCHAR(2), @BS_Month) + '-'
            + CONVERT(VARCHAR(2), @NpDay);
        RETURN (@Result);  

    END;
GO

Execute below function:
SELECT  dbo.[ufn_GetBikramSambatDate]('2018-04-14')

Upvotes: 3

I A Khan
I A Khan

Reputation: 8859

Nepali Calendar is based on Bikram Sambat and is 56 years and 8 months ahead of A.D. The Bikram Sambat calendar was started in 57 B.C. by King Bikramaditya in India.

there is an approximate difference of 57 years, 8 months and 16 days between these dates. To obtain BS date, you need to add 57 years, 8 months and 16 days to AD date. But the main problem is that there may be a difference of +/- 3 days

I Assuming 56 years and 8 months

SELECT GETDATE() AS 'English Date',
   DATEADD(MONTH,8,DATEADD(YEAR,56,GETDATE())) AS 'Nepali date'

for more information about DATEADD

visit http://sqlhints.com/2014/07/05/how-to-add-days-weeks-months-quarters-or-years-to-a-date-in-sql-server/

Upvotes: 0

Related Questions