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
>sql

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/06
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 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
>> >> >
>> >>
>> >>
>> >>
>>

I WANT TO DISPLAY ALL THE MONTHS BETWEEN 2 DATES FROM TABLE

Hi
My requirement is to display names all the months between two dates
from the table.
I have lots of START and STOP dates available in my table and I need
to display names of all the months including Start and Stop months.
How can I do that, with function or with some loop.
My database is SQL Server 2000 .
ThanksSee the DATENAME() function and SELECT DISTINCT.
Edmund
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>|||Edmund,
Here is a way to display the month names between @.start and @.stop
for two variables. You should be able to adapt this to your needs.
It requires a table containing the integers from 0 on up.
declare @.start datetime set @.start = '20050323'
declare @.stop datetime set @.stop = '20070223'
select
datename(month,dateadd(month,datediff(mo
nth,0,@.start)+n,0)) as MonthName
from Numbers1000
where n <= datediff(month,@.start,@.stop)
order by n
Here's how to generate the table of 1000 integers (you probably don't
need more)
create table Numbers1000(
n int primary key
)
declare @.a int set @.a = 0
while @.a < 1000 begin
insert into Numbers1000 values (@.a)
set @.a = @.a + 1
end
Steve Kass
Drew University
Edmund wrote:

>See the DATENAME() function and SELECT DISTINCT.
>Edmund
><akpatelrs@.googlemail.com> wrote in message
>news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
>
>
>|||Try this
declare @.date1 datetime,@.date2 datetime,@.str varchar(1000)
set @.str=''
set @.date1 = dateadd(mm,-4,getdate()) /* set it as yr start date */
set @.date2 = dateadd(mm,10,getdate()) /* set it as yr end date */
while datediff(mm,@.date1,@.date2)>0
begin
-- display month
set @.str= @.str+datename(mm,@.date1)+', '
-- decrease date
set @.date1 = dateadd(mm,1,@.date1)
end
declare @.strlen int
set @.strlen = len(@.str)
if @.strlen>1
select left(@.str,len(@.str)-2) as month_name
else
select @.str
hth
Peter
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>|||akpatelrs@.googlemail.com wrote:
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
Here is data from my table. For instance, for first start n stop date,
i need to display JAN,FEB like wise for second MAR,APR,MAY . This way
for all Start and Stop date from the table.
StartDate
StopDate
2007-01-01 00:00:00.000 2007-02-17
00:00:00.000
2006-03-01 13:38:46.397 2006-05-01
13:38:46.397
2006-05-19 03:18:57.060 2006-05-19
03:18:57.060|||You have a few suggestions already. Let me put my weight behind Steve's use
of an auxiliary table of numbers. There are all kinds of uses for it, it
doesn't take up much space, and it's ideal for situations like that which
you've got into. Populate it once, and then forget it's there. Just make
sure it's large enough - there may be times when you want it to be larger
than 1000. It's easy to top-up of course, but you don't want to find that
you discover it's too small when a client's report comes out short.
Rob
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>