Post reply

need sequence in datetime

  • May 24, 2011 at 1:34 pm

    #251497

    I am creating a table and I want sequential date ..ist possible?

    create table daysinfo(

    COMM1 datetime)

    insert into daysinfo values('1-05-2011')

    where 1 is day,05 month.

    and i want next date like (dont want to use insert)

    1-05-2011

    2-05-2011

    3-05-2011

    ist possible if we want daily attendance system and need all dates ?...i thought about many approach but please suggest some easy one

  • June 3, 2011 at 1:55 am

    #1333537

    Whats the significance of the data format, if you could format the same while showing in the front end using convert method?

  • June 3, 2011 at 3:10 am

    #1333552

    You might want to search this site for "calendar table" or you could have a look at the tally table article referenced in my signature (at the end there's an example on howto build a calendar table).


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 20, 2011 at 5:40 pm

    #1410452

    I’m not sure exactly what you are trying to accomplish but if you are looking for a script to automatically populate dates into a table I created and example below. It takes a Year as input and populates the table with all the dates in the year.

    If you are tracking daily attendance it may be useful to identify whether the date is a Weekend or a Holiday. I would also add an Identity column because to complete the relationship between Person and Attendance Date you will need some type of Cross Reference table to establish a Many-To-Many relationship that stores the PersonId and DaysInfoId so that you can track the attendance.

    SET NOCOUNT ON

    DECLARE @Year AS INT

    DECLARE @StartDate AS DATE

    DECLARE @IsWeekend AS BIT

    SET @Year = 2012

    SET @StartDate = CAST( '1/1/' + CAST(@Year AS CHAR(4)) AS DATE)

    WHILE(DATEPART(YYYY, @StartDate) = @Year)

    BEGIN

    SELECT @IsWeekend = CASE

    WHEN DATEPART(WEEKDAY, @StartDate) IN (7, 1)

    THEN 1

    ELSE 0

    END

    PRINT CAST(@StartDate AS VARCHAR) + ' IsWeekend: ' + CAST(@IsWeekend AS CHAR)

    IF NOT EXISTS(SELECT COMM1 FROM daysinfo WHERE COMM1 = @StartDate)

    BEGIN

    INSERT INTO daysinfo (COMM1, IsWeekend) VALUES(@StartDate,@IsWeekend)

    END

    SET @StartDate = DATEADD(DD, 1, @StartDate)

    END

Viewing 4 posts - 1 through 3 (of 3 total)

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