my query did not populate all dates

Question

Post reply

my query did not populate all dates

ramaanujamr

Old Hand

Points: 396
More actions
August 12, 2008 at 8:47 am

#196957

Hi, I am a beginner in sql server database. I inserted valus into calendar table for some time periods. but it didnot populate all dates .It does poputate until 6/28 only not 30 What is wrong in the query. Please advise me
declare @datetime1 as datetime
declare @datetime2 as datetime
set @datetime1='07/01/2007'
set @datetime2='06/30/2008'
while(dateadd(d,1,@datetime1) <>@datetime2)
begin
insert into Calendar values(
@datetime1,
datepart(Year,@datetime1), --Year
datepart(Month,@datetime1), --Month
datepart(Week,@datetime1), --Week
convert(nvarchar(10),datepart(year,@datetime1))+''+convert(nvarchar(10),datepart(Month,@datetime1)), --YearMonth
Substring(datename(Month,@datetime1),1,3), --MonthName
convert(nvarchar(10),datepart(year,@datetime1))+''+convert(nvarchar(10),datepart(Week,@datetime1)) ---Yearweek
)
print dateadd(d,1,@datetime1)
set @datetime1=dateadd(d,1,@datetime1)
end
Thanks
Ram

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

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

Mark Beringer-730383 SSCommitted Points: 1824 More actions · Answer 1

Change the WHILE condition to:

while(dateadd(d,1,@datetime1) <= @datetime2)

You had it as <> which means does not equal and thats why the last date was not inserted, <= will insert while datetime1 is less then or equal to datetime2.

ramaanujamr Old Hand Points: 396 More actions · Answer 2

Hi,

Still close. But it populates now until 2008-06-29 00:00:00.000 Not until 30th of june.

Time and seconds makes any difference?

Thanks

Ram

Mark Beringer-730383 SSCommitted Points: 1824 More actions · Answer 3

Remove the DateAdd from the WHILE

while @datetime1 <= @datetime2

ramaanujamr Old Hand Points: 396 More actions · Answer 4

Perfect. I think the reason is we already set dateadd for datetime1.

Thanks for your quick response.

Thanks.

Ram