Post reply

Filling a LIst table with Dates

  • June 28, 2010 at 10:41 am

    #223338

    Hello Everyone

    Something simple I know. I would like to fill one of my list tables with dates. The data type is date. I cannot seem to get this insert query to work properly. I can easily do this if I use SSIS to pump data in from a text file of equivalent. But I would like to perform this using a simple query.

    Thank you in advance for your help. My monday morning is starting out rough already.

    Andrew SQLDBA

  • June 28, 2010 at 10:46 am

    #1185926

    here's a simple and fast example: just add the insert into:

    --ten years before and after todays date:

    with TallyCalendar as (

    SELECT dateadd( dd,-3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N

    FROM (

    SELECT TOP 7300

    row_number() OVER (ORDER BY sc1.id) AS RW

    FROM Master.dbo.SysColumns sc1

    CROSS JOIN Master.dbo.SysColumns sc2

    ) X

    )

    --the SQL2008 DATE datatype, instead of datetime

    SELECT convert(date,TallyCalendar.N)

    from TallyCalendar

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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