Insert dates into table in the giving range.

  • Hi,

    I am having a table with SchoolDate column. In this column I need to enters all the dates in the range.

    for example start date = 02/25/14 and EndDate = 04/26/14 then i need to insert all the dates i.e.

    02/25/14

    02/26/14

    02/27/14

    .

    .

    .

    .

    .

    .

    04/26/14 .

    Could you please help me?

    Thanks

    Abhas.

  • We can use Identity for below ones only...

    data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type.

    http://technet.microsoft.com/en-us/library/ms189838.aspx for more info..

    Option 1:If you are inserting of records only...Add CHECK Constraint

    Option 2: For automatic population of Date in that column

    Create a tabular function which can take startdate and Enddate and gives result set as u expected.

    Update Ur Schooldate column with Resultset returned by function.

    !!!!!!!

    Sasidhar Pulivarthi

  • abhas (12/11/2013)


    Hi,

    I am having a table with SchoolDate column. In this column I need to enters all the dates in the range.

    for example start date = 02/25/14 and EndDate = 04/26/14 then i need to insert all the dates i.e.

    02/25/14

    02/26/14

    02/27/14

    .

    .

    .

    .

    .

    .

    04/26/14 .

    Could you please help me?

    Thanks

    Abhas.

    Here's a select statement to return a table of dates within a range. It uses a tally table. If you're not familiar with a tally table yet, read the article at http://www.sqlservercentral.com/articles/T-SQL/62867/ and take the time to get familiar with them. It'll change the way you look at data.

    select DATEADD(DAY, t.N - 1, CONVERT(datetime, '02/25/2014'))

    from Tally1K t

    where t.N < DATEDIFF(DAY, '02/24/2014', '04/26/2014') + 1

    order by 1;

  • If you don't want to create a Tally table, you can use sys.columns instead and create a tally number in memory. According to my research, this is actually faster than reading a Tally table from the disc.

    SELECTTOP(DATEDIFF(DAY, '02/24/2014', '04/26/2014') + 1)

    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, CONVERT(DATETIME, '02/24/2014')) AS ItemDate

    FROMsys.columns a CROSS JOIN sys.columns b

    The "CROSS APPLY" in the "FROM" clause may not be necessary if you are adding only a few values each time.

  • Or you can skip creating the table all together and use a calendar generating FUNCTION instead:

    CREATE FUNCTION [dbo].[GenerateCalendar]

    (

    @FromDate DATETIME,

    @NoDays INT

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    --===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)

    --===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)

    WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows

    E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows

    E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows

    cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)

    SELECT [SeqNo] = t.N,

    [Date] = dt.DT,

    [Year] = dp.YY,

    [YrNN] = dp.YY % 100,

    [YYYYMM] = dp.YY * 100 + dp.MM,

    [BuddhaYr] = dp.YY + 543,

    [Month] = dp.MM,

    [Day] = dp.DD,

    [WkDNo] = DATEPART(dw,dt.DT),

    [WkDName] = CONVERT(NCHAR(9),dp.DW),

    [WkDName2] = CONVERT(NCHAR(2),dp.DW),

    [WkDName3] = CONVERT(NCHAR(3),dp.DW),

    [JulDay] = dp.DY,

    [JulWk] = dp.DY/7+1,

    [WkNo] = dp.DD/7+1,

    [Qtr] = DATEPART(qq,dt.Dt),

    [Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,

    [LdOfMo] = DATEPART(dd,dp.LDtOfMo),

    [LDtOfMo] = dp.LDtOfMo

    FROM cteTally t

    CROSS APPLY ( --=== Create the date

    SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)

    ) dt

    CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"

    -- (Cascading CROSS APPLY, Acourtesy of ChrisM)

    SELECT YY = DATEPART(yy,dt.DT),

    MM = DATEPART(mm,dt.DT),

    DD = DATEPART(dd,dt.DT),

    DW = DATENAME(dw,dt.DT),

    Dy = DATEPART(dy,dt.DT),

    LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

    ) dp

    GO

    SELECT [Date]

    FROM dbo.GenerateCalendar('02/25/2014', 1+DATEDIFF(day, '04/26/2014', '02/25/2014'))

    ORDER BY [Date];


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Nice!

  • fahey.jonathan (12/12/2013)


    If you don't want to create a Tally table, you can use sys.columns instead and create a tally number in memory. According to my research, this is actually faster than reading a Tally table from the disc.

    SELECTTOP(DATEDIFF(DAY, '02/24/2014', '04/26/2014') + 1)

    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, CONVERT(DATETIME, '02/24/2014')) AS ItemDate

    FROMsys.columns a CROSS JOIN sys.columns b

    The "CROSS APPLY" in the "FROM" clause may not be necessary if you are adding only a few values each time.

    I'd actually like to see the proof of that, Jonathan. Normally, a small Tally Table of, say 11K rows or less will stay cached and won't actually be read from the disc. I'd also like to see the "form" of the Tally Table you tested with and the test code. People frequently forget such things as the addition of a Clustered Index on the Tally Table and/or to apply a properly pre-calculated limit (Like the TOP clause you used above) for values of "N" in the Tally Table.

    I'll also recommend that you use master.sys.all_columns instead of just sys.columns because it will always contain at least 4K rows even on a brand new system.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (1/1/2014)


    fahey.jonathan (12/12/2013)


    If you don't want to create a Tally table, you can use sys.columns instead and create a tally number in memory. According to my research, this is actually faster than reading a Tally table from the disc.

    SELECTTOP(DATEDIFF(DAY, '02/24/2014', '04/26/2014') + 1)

    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, CONVERT(DATETIME, '02/24/2014')) AS ItemDate

    FROMsys.columns a CROSS JOIN sys.columns b

    The "CROSS APPLY" in the "FROM" clause may not be necessary if you are adding only a few values each time.

    I'd actually like to see the proof of that, Jonathan. Normally, a small Tally Table of, say 11K rows or less will stay cached and won't actually be read from the disc. I'd also like to see the "form" of the Tally Table you tested with and the test code. People frequently forget such things as the addition of a Clustered Index on the Tally Table and/or to apply a properly pre-calculated limit (Like the TOP clause you used above) for values of "N" in the Tally Table.

    I'll also recommend that you use master.sys.all_columns instead of just sys.columns because it will always contain at least 4K rows even on a brand new system.

    I'd strongly recommend you don't use any sys. table at all for this, especially one in the master database. No need for such a complication for such a simple task.

    IIRC, Itzik Ben-Gan was the first to point out the performance advantages of using CROSS JOIN vs. reading from a physical tally table (and that a recursive CTE to gen the tally table was far worse than either of those options).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply