need sequence in datetime

• Tiya

  Ten Centuries

  Points: 1146

  More actions

  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

• Latheesh NK

  SSCertifiable

  Points: 6559

  More actions

  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?

• LutzM

  SSC Guru

  Points: 107049

  More actions

  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]

• Bill Hansen

  Ten Centuries

  Points: 1201

  More actions

  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 @YearAS INT

  DECLARE @StartDateAS DATE

  DECLARE @IsWeekendAS 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