Databases
StackOverflow2013
Postsdbo
POT-SQL task - find all dates with specified WeekDays in two dates range
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL task - find all dates with specified WeekDays in two dates range
    primarykey
    data
    text
    <p>I have quite tough task for me to do in T-SQL (MSSQL 2005). I have a table like this: </p> <pre><code>WeekDay| SlotTime ------------------ | 1 | 07:00 | 3 | 09:00 | 7 | 14:00 | 1 | 15:00 | 4 | 22:00 | 6 | 08:00 </code></pre> <p>where 1st column is WeekDay number and 2nd column is some Time value. </p> <p>As parameters for my query I have 2 dates, for example:</p> <pre><code>StartDate = '2011-07-20' EndDate = '2011-08-17' </code></pre> <p>This is a range definition for my data. I have to generate for these range all dates where WeekDay (from table above) happens and add to them SlotTime value. So for example, for above dates range result column should be:</p> <pre><code>2011-07-20 9:00 2011-07-21 22:00 2011-07-23 8:00 2011-07-24 14:00 2011-07-25 7:00 2011-07-25 15:00 2011-07-27 9:00 2011-07-28 22:00 2011-07-30 8:00 etc. ... </code></pre> <p>Any idea how to achieve this? Any tips? :) I'm considering this quite impossible without some huge(?) calculations and additional tables... </p> <p>Edit (maybe this snippet will help) I was playing with this function to use it as part of my calculations but was unable to achieve my goal. Maybe some part of this can be used in final solution...</p> <pre><code>create function dbo.NthWeekDay( @first datetime, -- First of the month of interest (no time part) @nth tinyint, -- Which of them - 1st, 2nd, etc. @dow tinyint -- Day of week we want ) returns datetime as begin -- Note: Returns a date in a later month if @nth is too large declare @result datetime set @result = @first + 7*(@nth-1) return @result + (7 + @dow - datepart(weekday,@result))%7 end go SET DATEFORMAT ymd SET DATEFIRST 1 select dbo.NthWeekDay('2011-07-20',1,1) as D go drop function NthWeekDay </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbinball
    1. USbinball
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POT-SQL task - find all dates with specified WeekDays in two dates range
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat defines what 1 is? Sunday, Monday, Thursday?
      singulars
      1. POT-SQL task - find all dates with specified WeekDays in two dates range
      1. USAsh Burlaczenko
    2. COsorry, it's for Monday. SET DATEFIRST 1 SET DATEFORMAT ymd
      singulars
      1. POT-SQL task - find all dates with specified WeekDays in two dates range
      1. USbinball
    3. COI can't think string right now so will leave an answer tomorrow if no one have by then. If you want to try it yourself it will need to use `DATEPART(dw, '<date>')`. I don't think it will be as complicated as you think.
      singulars
      1. POT-SQL task - find all dates with specified WeekDays in two dates range
      1. USAsh Burlaczenko
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload