I want to write a query return particular dates of the previous we

I want to write a query return particular dates of the previous week. Is
there some function where I can pass the weeknumber (I think last week was
37?) and get it to return these dates, regardless of what day of the week I
run the query?
Mon,9/11/06
Tues, 9/12/06
Wed, 9/13/06
Thurs, 9/14/06
Fri, 9/15/06SELECT
Column1
, Column2
, etc
FROM MyTable
WHERE datepart( wk, MyDateColumn ) = 37
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>I want to write a query return particular dates of the previous week. Is
> there some function where I can pass the weeknumber (I think last week was
> 37?) and get it to return these dates, regardless of what day of the week
> I
> run the query?
> Mon,9/11/06
> Tues, 9/12/06
> Wed, 9/13/06
> Thurs, 9/14/06
> Fri, 9/15/06
>|||Arnie,
I don't have a table, I only have the system or getdate to work with...
Perhaps I should rephrase the question:
I want to write a query return the workdays (Mon-Fri) of the previous week
base on getdate(). Ist here some function/select statement I can use where I
can pass the weeknumber (which I would get via the query (Select LAST_WEEK =(datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
Wed, Thurs, Fri, regardless of what day of the week I run the query?
"Arnie Rowland" wrote:
> SELECT
> Column1
> , Column2
> , etc
> FROM MyTable
> WHERE datepart( wk, MyDateColumn ) = 37
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
> >I want to write a query return particular dates of the previous week. Is
> > there some function where I can pass the weeknumber (I think last week was
> > 37?) and get it to return these dates, regardless of what day of the week
> > I
> > run the query?
> > Mon,9/11/06
> > Tues, 9/12/06
> > Wed, 9/13/06
> > Thurs, 9/14/06
> > Fri, 9/15/06
> >
>
>|||You may find the use of a Calendar table to be invaluable for exercises such
as this one.
Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519
Datetime -How to count the number of business days
http://www.aspfaq.com/show.asp?id=2453
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <DianeF@.discussions.microsoft.com> wrote in message
news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
> Arnie,
>
> I don't have a table, I only have the system or getdate to work with...
> Perhaps I should rephrase the question:
> I want to write a query return the workdays (Mon-Fri) of the previous week
> base on getdate(). Ist here some function/select statement I can use
> where I
> can pass the weeknumber (which I would get via the query (Select LAST_WEEK
> => (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
> Wed, Thurs, Fri, regardless of what day of the week I run the query?
>
> "Arnie Rowland" wrote:
>> SELECT
>> Column1
>> , Column2
>> , etc
>> FROM MyTable
>> WHERE datepart( wk, MyDateColumn ) = 37
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
>> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>> >I want to write a query return particular dates of the previous week.
>> >Is
>> > there some function where I can pass the weeknumber (I think last week
>> > was
>> > 37?) and get it to return these dates, regardless of what day of the
>> > week
>> > I
>> > run the query?
>> > Mon,9/11/06
>> > Tues, 9/12/06
>> > Wed, 9/13/06
>> > Thurs, 9/14/06
>> > Fri, 9/15/06
>> >
>>|||I understand, but is there a query I can write that will accomplish the same
thing?
"Arnie Rowland" wrote:
> You may find the use of a Calendar table to be invaluable for exercises such
> as this one.
> Datetime -Calendar Table
> http://www.aspfaq.com/show.asp?id=2519
> Datetime -How to count the number of business days
> http://www.aspfaq.com/show.asp?id=2453
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Diane F." <DianeF@.discussions.microsoft.com> wrote in message
> news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
> > Arnie,
> >
> >
> > I don't have a table, I only have the system or getdate to work with...
> > Perhaps I should rephrase the question:
> > I want to write a query return the workdays (Mon-Fri) of the previous week
> > base on getdate(). Ist here some function/select statement I can use
> > where I
> > can pass the weeknumber (which I would get via the query (Select LAST_WEEK
> > => > (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
> > Wed, Thurs, Fri, regardless of what day of the week I run the query?
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> SELECT
> >> Column1
> >> , Column2
> >> , etc
> >> FROM MyTable
> >> WHERE datepart( wk, MyDateColumn ) = 37
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
> >> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
> >> >I want to write a query return particular dates of the previous week.
> >> >Is
> >> > there some function where I can pass the weeknumber (I think last week
> >> > was
> >> > 37?) and get it to return these dates, regardless of what day of the
> >> > week
> >> > I
> >> > run the query?
> >> > Mon,9/11/06
> >> > Tues, 9/12/06
> >> > Wed, 9/13/06
> >> > Thurs, 9/14/06
> >> > Fri, 9/15/06
> >> >
> >>
> >>
> >>
>
>|||I don't think that any such 'function' exists, and a query to accomplish
your goal would be quite Byzantine.
I suppose that you could create your own user defined function, but since
using a Calendar table makes the process so easy, I don't waste my time
trying to find kludges like that -and I recommend that you don't as well.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <DianeF@.discussions.microsoft.com> wrote in message
news:BA5F9A11-0879-4F0A-B84E-B844D68C945C@.microsoft.com...
>I understand, but is there a query I can write that will accomplish the
>same
> thing?
> "Arnie Rowland" wrote:
>> You may find the use of a Calendar table to be invaluable for exercises
>> such
>> as this one.
>> Datetime -Calendar Table
>> http://www.aspfaq.com/show.asp?id=2519
>> Datetime -How to count the number of business days
>> http://www.aspfaq.com/show.asp?id=2453
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Diane F." <DianeF@.discussions.microsoft.com> wrote in message
>> news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
>> > Arnie,
>> >
>> >
>> > I don't have a table, I only have the system or getdate to work with...
>> > Perhaps I should rephrase the question:
>> > I want to write a query return the workdays (Mon-Fri) of the previous
>> > week
>> > base on getdate(). Ist here some function/select statement I can use
>> > where I
>> > can pass the weeknumber (which I would get via the query (Select
>> > LAST_WEEK
>> > =>> > (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon,
>> > Tues,
>> > Wed, Thurs, Fri, regardless of what day of the week I run the query?
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> SELECT
>> >> Column1
>> >> , Column2
>> >> , etc
>> >> FROM MyTable
>> >> WHERE datepart( wk, MyDateColumn ) = 37
>> >>
>> >> --
>> >> Arnie Rowland, Ph.D.
>> >> Westwood Consulting, Inc
>> >>
>> >> Most good judgment comes from experience.
>> >> Most experience comes from bad judgment.
>> >> - Anonymous
>> >>
>> >>
>> >> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
>> >> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>> >> >I want to write a query return particular dates of the previous week.
>> >> >Is
>> >> > there some function where I can pass the weeknumber (I think last
>> >> > week
>> >> > was
>> >> > 37?) and get it to return these dates, regardless of what day of the
>> >> > week
>> >> > I
>> >> > run the query?
>> >> > Mon,9/11/06
>> >> > Tues, 9/12/06
>> >> > Wed, 9/13/06
>> >> > Thurs, 9/14/06
>> >> > Fri, 9/15/06
>> >> >
>> >>
>> >>
>> >>
>>