INSERTing a row for each day of current month

[cmhunt]

Hi

I have a table and I want to INSERT a row for each day of the current month with the field Date containing just the number in. For example in Jan, I would have 31 rows with the field Date having the numbers 1 - 31. I am currently doing this by taking the current month and starting at 1st of the month, using DATEADD to add a day until the month changes and INSERTing a line for each day. It works fine but I was wondering if anyone could think of a simpler way of doing this.

Thanks

Chris

 

[hubud]

I take it that your not using this field as a UID and that the field is a Datetime data type. If this is so could you not use something like:

day(getdate())

This will give you the days number part of the getdate() function.
Hope this helps

 

[thegiraffe]

not sure on any of the actual coding but could you dateadd -1 day from the first of the following month to give you the last day of the month and assign the last day (day(variable)) to a variable (eg. lastday).

then assign a vairable as 1 (eg. curday)

then do a for loop
for curday to lastday
insert record into table
loop

hope this is of some help. Dave

Theres nothing worse than an idea when its the only one we have!

 

[cmhunt]

Thanks guys.

Hubud, I was already using that to obtain the number, then adding to the date.

Giraffe, I have no idea why I didn't think of that!! Same theory as mine but makes the code a little easier. Will post the code when I have done in case anyone was interested. Won't be very complicated though!

Thanks again!

Chris