Adding dates within SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Adding dates within SELECT

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-03-02 : 06:09:09

Hi all,

This is a theoretical question to assist me with designing a schema. I have a table of targets that a person must achieve within a set time period (e.g. user must have completed a training package within 6 months from today).

So I have the following schema:


ID int PK
UserID int FK
TargetDesc nvarchar
TargetSet datetime -- the day that the target was set
CompletionD int -- how many days from today the user has to complete their target
CompletionM int -- how many months from today the user has to complete their target
CompletionY int -- how many years from today the user has to complete their target
Completed bit

In a year's time, I need to run a SELECT statement to find out which targets are incomplete. Is it possible to do three DATEADD functions on a value in one operation? e.g. would this work?


SELECT 
	t.TargetDesc,
	u.userID
FROM
	tblTargets t INNER JOIN
	tblUsers u ON t.userID = u.userID
WHERE
	(t.Completed = 0) AND
	(DATEADD (Day, CompletionD, DATEADD (Month, CompletionM, DATEADD (Year, CompletionY, t.DateSet))) >= getDate())

If I had a couple of hundred rows that were being selected, would it be too much of a performance hit performing the DATEADD function three times per row? Would it be best to calculate the overdue date in a new column in each row instead?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 10:17:16

its possible. however it would be best if you can prestore value as a date rather than as years,days,months and adding each of them.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-03-02 : 11:10:18

Thanks visakh16

To be honest after thinking about it, using a simple Target Date column would be equally as obvious to the user, but much more efficient for the database engine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources