changing dates - 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

General SQL Server Forums

New to SQL Server Programming

changing dates

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-02-08 : 15:16:46

I have a where class that compares an expiration date to the current date, so I don't pull expired data. I am wondering if there is any way to write something that could 'change' the expiration date (in the query check only, not the actual table) that if the date falls on a saturday or sunday, the expiration date would be the preceeding fri.

My where clause is: WHERE ExpDate > GETTDATE

Is it possible to do something like this: WHERE IF(datepart(expdate)=*,expdate=*)...

Sorry, I am more into excel programming at the moment.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 11:39:47

If you want to change ExpDate, try


WHERE 
dateadd
(
day,
case when datename(weekday,ExpDate)='Saturday' then -1
     when datename(weekday,ExpDate)='Sunday' then -2
else
    0
end,
ExpDate
)> GETTDATE

PS : The above will work only if the default language of the Server is English

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources