0

Say starting date is 8/04/2010 ending date is 8/04/2011 I need the dates of saturdays and sundays to be inserted in a table... Need Help

surya
  • 1
  • 1

1 Answers1

2

Use an unambiguous date format, like '2010-08-04'. This sorts correctly as either date or text, and everybody knows it means 04-Aug, not 08-Apr.

I like to use a calendar table for queries like this. This will select the dates.

select cal_date
from calendar
where cal_date between '2010-08-04' and '2011-08-04'
and (day_of_week = 'Sat' or day_of_week = 'Sun');

Something like this will insert them into a table. (Depending on the table.)

insert into your_table_name
select cal_date
from calendar
where cal_date between '2010-08-04' and '2011-08-04'
and (day_of_week = 'Sat' or day_of_week = 'Sun');
Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 2
    Everyone knows it is 04-Aug, except for SQL Server, which will return 4 for this query: `set language british select DATEPART(month,CONVERT(datetime,'2010-08-04'))`. The unambiguous date only format for converting to datetime doesn't include dashes between the components, e.g. `'20100804'` – Damien_The_Unbeliever Apr 12 '11 at 10:48
  • @Damien_The_Unbeliever: You're right. The format 'yyyy-mm-dd' is just the SQL standard. – Mike Sherrill 'Cat Recall' Apr 12 '11 at 11:18