Displaying dates

[IT4EVR]

I'm doing a time tracking application and I want to be able to display all dates 14 days prior to a particular pay_end_date.

Only the pay_end_date would be in a database table. So if the pay_end_date is 09/16/2006, I want to show:

09/03/2006
09/04/2006
...
09/16/2006

I'm sure there is a way to do this. Thanks...

 

[SQLSister]

Are they stored as datetime datatyypes?

then you can do date math, look up dateadd and datediff in books on line.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

If pay_end_date is stored as a DateTime field, then you could simply...

Select pay_end_date - 14 As 2WeeksBeforePayEnd
From   Table

Internally, dates are stored as a number, where the whole number part represents the number of days that have elapsed since Jan 1, 1900 and the fractional part represents the fractional part of day (i.e. time).

You may also want to take a look at the DayAdd function. Look it up in books on line.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Correction, that's dateadd.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[IT4EVR]

Gmmastros, I'm familiar with the dateadd, but that's only going to give me one date. I want the inclusive dates from 14 days prior to the pay end date. The inclusive dates are not stored in the database, only the pay end date, which is a smalldatetime datatype.

 

[gmmastros]

My apologies for not understanding your original question. This can be accomplished by using a numbers table. In the example I provide, I create a numbers table as a table variable. It is recommended that you have a permanent numbers table in your database. Even with a million records, it is relatively small and makes writing these types of queries a lot easier.

[green]-- Sample data[/green]
Declare @Temp Table(Id integer, pay_end_date DateTime)

Insert Into @Temp Values(1, '20060916')
insert into @temp Values(2, '20060916')

[green]-- The query[/green]

Declare @Num Table(RowId integer Identity(1,1))

Declare @i Integer

Set @i = 0

While @i <= 14
  Begin
    Insert Into @Num Default Values
    Set @i = @i + 1
  End


Select *, 
       pay_end_date - num.rowid
from   @Num As Num
       Cross Join @Temp As T

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zeevgetner]

i suggest that you have a table with one field of tpe date time,
and you will enter to it all the dates in the next 100 years,
and then you can do what you want without any problem.

 

[IT4EVR]

Mastros, one question, why are you inserting the same datevalue more than once in the temp table?

 

[gmmastros]

I wanted to make sure that the query would work properly. When using a cross join, it's important to test it thoroughly.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom