get todays date - 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 2008 Forums

Transact-SQL (2008)

get todays date

Author

Topic

rsql
Starting Member

6 Posts

Posted - 2011-10-19 : 15:08:45

Does anyone has a function which returs only working day? I mean M,T,W,Th,F.

So on Monday I want to run my script for Friday and not for sunday.

Any help will be appreciated.

Thanks

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-10-20 : 04:12:34

Have a look at this code:

SET DATEFIRST 1

DECLARE @dd DATETIME

IF DATEPART(WEEKDAY,GETDATE()) = 1
BEGIN
	SET @dd = DATEADD(DAY,-3,DATEDIFF(DAY,0,GETDATE()))
END
ELSE
BEGIN
	SET @dd = DATEADD(DAY,-1,DATEDIFF(DAY,0,GETDATE()))
END

SELECT DATENAME(WEEKDAY,@dd),DATEPART(WEEKDAY,@dd),@dd

The first line the SET DATEFIRST 1 assigns a number to a day of the week, in this case, I have set Monday as 1, Tuesday as 2, Wednesday as 3 etc.
Then the query looks at the weekday number, if it is 1, it uses the DATEADD function to pick a date 3 days ago, and if it is 2-7 it simply uses the previous day.
The variable @dd, can then be used in your main query to select your data e.g. WHERE mydate = @dd

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources