Very Computer

by **Brian Russel** » Fri, 07 Jun 2002 22:02:58

I need a query to list all dates from a certain date. I am not sure if this
is possible. I want this for a report that will show every monday from a
certain date and then I will have a sub-report for the detail if there is
any. The dates will show me the ones I do not have detail for.

by **Anith Se** » Fri, 07 Jun 2002 22:16:08

Something like this?

SET DATEFIRST 1
SELECT
dateCol,
CASE DATEPART(dw, dateCol)
WHEN 1 THEN 'YES
ELSE 'NO'
END AS [IsMonday]
FROM
yourTable

- Anith

Quote:> I need a query to list all dates from a certain date. I am not sure if
this
> is possible. I want this for a report that will show every monday from a
> certain date and then I will have a sub-report for the detail if there is
> any. The dates will show me the ones I do not have detail for.

by **Brian Russel** » Fri, 07 Jun 2002 22:40:05

It looks like this will tell me if a date in my table is a Monday. If I am
wrong please tell me.
What I need is a list of all days from like 1/1/2002 till now. I do not
have a table that contains these. I just want to know if there is a way
with a query or a stored procedure to get a list of everyday from a date I
give it. Like this. I would rather it be a query but that may be
impossible. I should have explained it better. Thanks for your help.

sp_listalldaysfrom('1/1/2002')

1/1/2002
1/2/2002
1/3/2002
...

> Something like this?

> SET DATEFIRST 1
> SELECT
> dateCol,
> CASE DATEPART(dw, dateCol)
> WHEN 1 THEN 'YES
> ELSE 'NO'
> END AS [IsMonday]
> FROM
> yourTable

> - Anith

> > I need a query to list all dates from a certain date. I am not sure if
> this
> > is possible. I want this for a report that will show every monday from
a
> > certain date and then I will have a sub-report for the detail if there
is
> > any. The dates will show me the ones I do not have detail for.

by **Anith Se** » Fri, 07 Jun 2002 22:50:15

How about...

SET DATEFIRST 1

BEGIN
SELECT

WHEN 1 THEN 'YES'
ELSE 'NO' END AS [IsMonday]

END

Or if you need to use them in JOINs , create a #temp
table

SET DATEFIRST 1
CREATE TABLE #Temp(testDate DATETIME)

BEGIN

END
SELECT
testDate,
CASE DATEPART(dw, testDate)
WHEN 1 THEN 'YES'
ELSE 'NO' END AS [IsMonday]
FROM
#Temp

- Anith

> It looks like this will tell me if a date in my table is a Monday. If I
am
> wrong please tell me.
> What I need is a list of all days from like 1/1/2002 till now. I do not
> have a table that contains these. I just want to know if there is a way
> with a query or a stored procedure to get a list of everyday from a date I
> give it. Like this. I would rather it be a query but that may be
> impossible. I should have explained it better. Thanks for your help.

> sp_listalldaysfrom('1/1/2002')

> 1/1/2002
> 1/2/2002
> 1/3/2002
> ...

> > Something like this?

> > SET DATEFIRST 1
> > SELECT
> > dateCol,
> > CASE DATEPART(dw, dateCol)
> > WHEN 1 THEN 'YES
> > ELSE 'NO'
> > END AS [IsMonday]
> > FROM
> > yourTable

> > - Anith

> > > I need a query to list all dates from a certain date. I am not sure
if
> > this
> > > is possible. I want this for a report that will show every monday
from
> a
> > > certain date and then I will have a sub-report for the detail if there
> is
> > > any. The dates will show me the ones I do not have detail for.

by **Rayda** » Fri, 07 Jun 2002 22:50:57

Just to get you started, here's one method:
declare startdate = '1/1/2002'
declare enddate = today

declare tempTable with one date column

insert into tempTable startdate

while startdate < end date
set startdate = startdate + 1
insert into tempTable startdate
end

select * from tempTable

by **Rayda** » Fri, 07 Jun 2002 22:56:36

I guess I got lazy when I answered.
And sorry, I missed the Monday part of the question.

I need a coffee.

> How about...

> SET DATEFIRST 1

> BEGIN
> SELECT

> WHEN 1 THEN 'YES'
> ELSE 'NO' END AS [IsMonday]

> END

> Or if you need to use them in JOINs , create a #temp
> table

> SET DATEFIRST 1
> CREATE TABLE #Temp(testDate DATETIME)

> BEGIN

> END
> SELECT
> testDate,
> CASE DATEPART(dw, testDate)
> WHEN 1 THEN 'YES'
> ELSE 'NO' END AS [IsMonday]
> FROM
> #Temp

> - Anith

by **Anith Se** » Fri, 07 Jun 2002 23:03:13

Cheer up! It happens to me all the time
"HAVE COFFEE, BE HAPPY" :-)

- Anith

> I guess I got lazy when I answered.
> And sorry, I missed the Monday part of the question.

> I need a coffee.

> > How about...

> > SET DATEFIRST 1

> > BEGIN
> > SELECT

> > WHEN 1 THEN 'YES'
> > ELSE 'NO' END AS [IsMonday]

> > END

> > Or if you need to use them in JOINs , create a #temp
> > table

> > SET DATEFIRST 1
> > CREATE TABLE #Temp(testDate DATETIME)

> > BEGIN

> > END
> > SELECT
> > testDate,
> > CASE DATEPART(dw, testDate)
> > WHEN 1 THEN 'YES'
> > ELSE 'NO' END AS [IsMonday]
> > FROM
> > #Temp

> > - Anith

by **Brian Russel** » Fri, 07 Jun 2002 23:25:34

I think I will take what both of you gave me and create a table of dates
with Mondays.
I was hoping there was another way but It doesn't look like this will work
from my report.

Thanks for your help.

> Cheer up! It happens to me all the time
> "HAVE COFFEE, BE HAPPY" :-)

> - Anith

> > I guess I got lazy when I answered.
> > And sorry, I missed the Monday part of the question.

> > I need a coffee.

> > > How about...

> > > SET DATEFIRST 1

> > > BEGIN
> > > SELECT

> > > WHEN 1 THEN 'YES'
> > > ELSE 'NO' END AS [IsMonday]

> > > END

> > > Or if you need to use them in JOINs , create a #temp
> > > table

> > > SET DATEFIRST 1
> > > CREATE TABLE #Temp(testDate DATETIME)

> > > BEGIN

> > > END
> > > SELECT
> > > testDate,
> > > CASE DATEPART(dw, testDate)
> > > WHEN 1 THEN 'YES'
> > > ELSE 'NO' END AS [IsMonday]
> > > FROM
> > > #Temp

> > > - Anith

by **Brian Russel** » Fri, 07 Jun 2002 23:42:26

begin

begin

end

end

> I think I will take what both of you gave me and create a table of dates
> with Mondays.
> I was hoping there was another way but It doesn't look like this will work
> from my report.

> Thanks for your help.

> > Cheer up! It happens to me all the time
> > "HAVE COFFEE, BE HAPPY" :-)

> > - Anith

> > > I guess I got lazy when I answered.
> > > And sorry, I missed the Monday part of the question.

> > > I need a coffee.

> > > > How about...

> > > > SET DATEFIRST 1

> > > > BEGIN
> > > > SELECT

> > > > WHEN 1 THEN 'YES'
> > > > ELSE 'NO' END AS [IsMonday]

> > > > END

> > > > Or if you need to use them in JOINs , create a #temp
> > > > table

> > > > SET DATEFIRST 1
> > > > CREATE TABLE #Temp(testDate DATETIME)

> > > > BEGIN

> > > > END
> > > > SELECT
> > > > testDate,
> > > > CASE DATEPART(dw, testDate)
> > > > WHEN 1 THEN 'YES'
> > > > ELSE 'NO' END AS [IsMonday]
> > > > FROM
> > > > #Temp

> > > > - Anith

by **Tom Furnes** » Fri, 07 Jun 2002 23:51:36

You could create a table of mondays and then do a join
with a where clause "between <you_start_date> and <getdate>

> I think I will take what both of you gave me and create a table of dates
> with Mondays.
> I was hoping there was another way but It doesn't look like this will work
> from my report.

> Thanks for your help.

> > Cheer up! It happens to me all the time
> > "HAVE COFFEE, BE HAPPY" :-)

> > - Anith

> > > I guess I got lazy when I answered.
> > > And sorry, I missed the Monday part of the question.

> > > I need a coffee.

> > > > How about...

> > > > SET DATEFIRST 1

> > > > BEGIN
> > > > SELECT

> > > > WHEN 1 THEN 'YES'
> > > > ELSE 'NO' END AS [IsMonday]

> > > > END

> > > > Or if you need to use them in JOINs , create a #temp
> > > > table

> > > > SET DATEFIRST 1
> > > > CREATE TABLE #Temp(testDate DATETIME)

> > > > BEGIN

> > > > END
> > > > SELECT
> > > > testDate,
> > > > CASE DATEPART(dw, testDate)
> > > > WHEN 1 THEN 'YES'
> > > > ELSE 'NO' END AS [IsMonday]
> > > > FROM
> > > > #Temp

> > > > - Anith

by **Harlan Messinge** » Sat, 08 Jun 2002 02:29:43

>It looks like this will tell me if a date in my table is a Monday. If I am
>wrong please tell me.
>What I need is a list of all days from like 1/1/2002 till now. I do not
>have a table that contains these. I just want to know if there is a way
>with a query or a stored procedure to get a list of everyday from a date I
>give it. Like this. I would rather it be a query but that may be
>impossible.

A query returns rows from one or more tables (except for a query with
all scalar columns that returns one row). No table, no query.

Also, your request is for a means of returning an infinite number of
results, since there willl be infinite Mondays after, say, next
Monday. Doesn't seem very practical. :)

> I should have explained it better. Thanks for your help.

>sp_listalldaysfrom('1/1/2002')

> 1/1/2002
> 1/2/2002
> 1/3/2002
> ...

>> Something like this?

>> SET DATEFIRST 1
>> SELECT
>> dateCol,
>> CASE DATEPART(dw, dateCol)
>> WHEN 1 THEN 'YES
>> ELSE 'NO'
>> END AS [IsMonday]
>> FROM
>> yourTable

>> - Anith

>> > I need a query to list all dates from a certain date. I am not sure if
>> this
>> > is possible. I want this for a report that will show every monday from
>a
>> > certain date and then I will have a sub-report for the detail if there
>is
>> > any. The dates will show me the ones I do not have detail for.

--
Harlan Messinger
Remove the first dot from my e-mail address.
Veuillez ?ter le premier point de mon adresse de courriel.

Very Computer

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates

Query to List Dates