Home 2018 May SQL Puzzle: Generate Calendar Data for 19th Century

SQL Puzzle: Generate Calendar Data for 19th Century

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output for generating the complete calendar data for 19th century.

Input Date: 19000101

Expected Output:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Puzzle: Use Recursive CTE, and list out the Years from DatesSQL Server Coding Standards: About Object Identifiers
Comments: 3
  1. Ovidiu-Ionel Samfirescu
    January 14, 2019 at 8:45 pm

    DECLARE @INPUTDATE INT = 19000101
    DECLARE @TABLE TABLE (CalendarDATE DATE)
    INSERT INTO @TABLE
    SELECT CAST(SUBSTRING(CAST(@INPUTDATE AS CHAR),1,4) + ‘-‘ + SUBSTRING(CAST(@INPUTDATE AS CHAR),5,2) + ‘-‘ + SUBSTRING(CAST(@INPUTDATE AS CHAR),7,8) AS DATE)
    WHILE (SELECT MAX(CalendarDATE) FROM @TABLE) < '2001-01-01'
    BEGIN
    INSERT INTO @TABLE
    SELECT DATEADD(DAY,1,(SELECT MAX(CalendarDATE) FROM @TABLE))
    DECLARE @PRINT NVARCHAR(100) = (SELECT CAST(MAX(CalendarDATE) AS NVARCHAR) FROM @TABLE)
    PRINT @PRINT
    END
    SELECT * FROM @TABLE

    ReplyCancel
  2. Kati Novikov
    February 12, 2019 at 6:04 pm

    declare @dt date = ‘1900-01-01’
    create table #date (dt date);

    while @dt < getdate()
    begin
    insert into #date (dt) values (@dt);
    set @dt = DATEADD(day, 1, @dt);
    end

    select * from #date

    ReplyCancel
  3. HIC
    April 16, 2019 at 10:09 am

    WITH calendar(fdate) AS (
    SELECT to_date(‘01.01.1900’, ‘dd.mm.yyyy’) as fdate from dual
    UNION ALL
    SELECT fdate + 1 FROM calendar
    WHERE fdate + 1 <= sysdate
    )
    SELECT fdate FROM calendar;

    ReplyCancel

Leave a Reply to Ovidiu-Ionel Samfirescu Cancel reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

May 15, 2018 3 Comments SQL Puzzle, , , , , , , , , ,