0

Possible Duplicate:
how to insert record into database on single button click from date to todate ?

I have two textbox .... textbox1 and textbox2

textbox1 == choose from date textbox2 == choose to date

if user select from date in textbox1 as 01-May-2011 and in textbox2 as 30-May-2011

then all the dates from 01-MAy-2011 to 30-May-2011 will be inserted in each row of mssql2005 datatable...

example. . :

IN database Table1 structure ...

ID           Date
1            01-MAy-2011
2            02-MAy-2011
3            03-MAy-2011
4            04-MAy-2011
5            05-MAy-2011

and so on till 30-May-2011

Community
  • 1
  • 1
Mr. Goo
  • 1
  • 1
  • I wanna inset dates between ttwo dates 1 by one in database row .. – Mr. Goo Mar 13 '11 at 08:22
  • yes, your question is well understood. but you need to tell us your specific problem, i.e. what you have implemented so far and where exactly you are stuck. people are not very likely to build a whole program for you here. – Mat Mar 13 '11 at 08:24
  • i DONT KNOW THE CODING ... HOW TO INSERT the dates between two dates on database each row on button click... – Mr. Goo Mar 13 '11 at 08:27
  • please don't shout. how are you going about inserting one of the rows? this will let people show you ways of inserting the next ones. – Mat Mar 13 '11 at 08:37
  • I didnt understand. ... what r u taking above above ? – Mr. Goo Mar 13 '11 at 08:47

1 Answers1

1

Use a recursive CTE?

DECLARE @date1 datetime, @date2 datetime --use date with SQL Server 2008
SELECT @date1 = '20110501', @date2 = '20110530' 

;WITH cDateRange AS
(
   SELECT
      @date1 AS TheDate
   UNION ALL
   SELECT
      DATEADD(day, 1, TheDate) -- keep in the date/time domain. No implicit CASTs
   FROM
      cDateRange
   WHERE
      DATEADD(day, 1, TheDate) <= @date2
      -- no need: we are comparing same datatypes DATEDIFF(day, DATEADD(day, 1, TheDate), @date2) >= 0
)
INSERT mytable (ID, TheDate)
SELECT 
    --Use ROW_NUMBER to deal with month boundaries
    ROW_NUMBER() OVER (ORDER BY TheDate) AS [id],
    TheDate
FROM
    cDateRange
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    Just curious: did you intentionally choose to use DATEADD and DATEDIFF in this answer instead of simpler date arithmetics and comparisons or do you actually prefer using them in your coding? I mean, `TheDate + 1` and `TheDate < @date2` look simpler and clearer, which I'm sure you're aware of, so I'm just trying to understand the reasoning behind those choices of yours. – Andriy M Mar 13 '11 at 12:21
  • 1
    One more thing: in this particular case it would be enough to use `DAY(TheDate) AS id` (or `DATEPART(day, TheDate) AS id`, if you like). – Andriy M Mar 13 '11 at 12:24
  • 1
    @Andriy M: Good point. I'd use use DATEADD to avoid +1 because that requires an implicit CASE. I should have used `<` because I would be comparing like for like. Please excuse my Sunday brain-off mode. If the start date is 14 May 2011 and the end date is 05 Jul 2011 then the DAY or DATEPART would fail. And updated – gbn Mar 13 '11 at 12:24
  • 1
    Thanks to your post and, in particular, the clarifying comment I've come to know that `date is incompatible with int`, as the error message goes. So far I haven't been working on projects that use the `date` type, so I haven't been used to it and instead I've been used to adding `int`s to `datetime`s. So it's a good thing to know in advance that DATEADD is the way to go. Thank you! And I'm afraid I cannot upvote your answer for the very useful comments you've added there, because I so thoughtlessly upvoted it earlier. Sorry. – Andriy M Mar 13 '11 at 13:15
  • And I see the point in using ROW_NUMBER. Of course that makes the solution more scalable. – Andriy M Mar 13 '11 at 13:17
  • @Andriy M: Glad to help. I summarise why I use DATEADD/DATEDIFF for this kind of stuff here. http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server/1177529#1177529 and this http://stackoverflow.com/questions/3596663/why-is-my-case-expression-non-deterministic/3596685#3596685 – gbn Mar 13 '11 at 13:20