Date Issues - 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)

Date Issues

Author

Topic

rollypolly
Starting Member

2 Posts

Posted - 2010-09-28 : 05:13:20

I want to allocate Monday's date to the week running Monday to Sunday in a view. What do you suggest?
In the view I am currently trying:
DATEADD(d, 1, dbo.GetFirstDayOfWeek(CalendarBase.TheDate)) AS fieldname (function below)

ALTER FUNCTION [dbo].[GetFirstDayOfWeek]
( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate),@pInputDate)

END

That is allocating Monday's date to the week running Sunday to Saturday. I tried to use datefirst with no luck

Rolly Polly

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-28 : 06:48:13

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @pInputDate) / 7 * 7, 0)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources