Query to List Dates

Query to List Dates

Post 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.
 
 
 

Query to List Dates

Post 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.


 
 
 

Query to List Dates

Post 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.

 
 
 

Query to List Dates

Post 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.

 
 
 

Query to List Dates

Post 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

 
 
 

Query to List Dates

Post 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

 
 
 

Query to List Dates

Post 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

 
 
 

Query to List Dates

Post 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

 
 
 

Query to List Dates

Post 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

 
 
 

Query to List Dates

Post 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

 
 
 

Query to List Dates

Post 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.