CODEWITHSUNDEEP
sqlsql-server
Rachid
Rachid

Reputation: 67

stored procedure to fill calendar

I'd like to fill a table "calendar" in a SQL Server with a click on a button in an vb.net form, I have to fill next columns: date, week_weekend (is this date a week or weekend day), name of the day (monday, tuesday,...), period_name (season, holiday), code_schoolholiday (y or N), code_holiday (christmas,newyear,etc..)

Upvotes: 0

Views: 707

Answers (1)

jakubiszon
jakubiszon

Reputation: 3573

To find the day of the week use select DATEPART(weekday, _date_goes_here_) this gives you a number representing the day. You can use following code to get the day name for today:

-- EDIT as Martin rightly pointed out, you need to take
-- @@datefirst into account here's a version of code which will 
-- return the right dayname regardless of this variable's value
select case DATEPART(weekday, getdate() + @@datefirst)
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
end

You can also easily find out if the day is the weekend:

select case DATEPART(weekday, getdate())
when 1 then 'weekend'
when 7 then 'weekend'
else 'weekday'
end

More information about DATEPART function at MSDN

Now to insert a lot of rows with lots of dates and computed data you need a variation of following code (which selects today and following 99 days, and of course you need to wrap it with INSERT statement):

select v1.x * 10 + v2.x as dayoffset
,      cast((GETDATE() + v1.x * 10 + v2.x) as DATE) as calendardate
,   case DATEPART(weekday, cast((GETDATE() + v1.x * 10 + v2.x) as DATE))
    when 1 then 'Sunday'
    when 2 then 'Monday'
    when 3 then 'Tuesday'
    when 4 then 'Wednesday'
    when 5 then 'Thursday'
    when 6 then 'Friday'
    when 7 then 'Saturday'
    end as dayname
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) v1(x)
cross join (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) v2(x)
order by v1.x * 10 + v2.x

You need to narrow down your requirements for the rest of the data:

  1. Define the names of the seasons and criteria to decide which season each day is - I believe you can easily use the DATEPART function again here.
  2. School holidays happen on different days per country or even per regions of bigger countries. Narrow it down. Also in some countries (like Poland) in some years there are additional free days added between weekends and bank holidays to make a longer holiday period and in exchange the children go to school on a Saturday the week after.
  3. Define what you understand as holidays - free days? Days when special religious ceremonies/events take place? If so then which religion, country, religion flavor? I belive that once again DATEPART function will be your good friend here.

To determine new year's eve:

select d, case
  when DATEPART(month, d) = 12 and DATEPART(DAY, d) = 31 then 'New years eve'
  else 'other day'
  end
from (
  select CAST('2010-12-31' as datetime)
  union
  select GETDATE()
) t(d)

Upvotes: 1

Related Questions