Determine Monday

In SQL Server, (Monday mod 7) = 0.

-- Microsoft SQL Server 2000
-- Determine Monday

SET DATEFIRST 7
PRINT @@DATEFIRST

-- Method One --------------------------------------------------
DECLARE @value AS INT

SET @value = FLOOR(CAST(GETDATE() AS FLOAT)) + 1
PRINT CAST(@value - (@value % 7) AS DATETIME)

 


-- Method Two --------------------------------------------------
DECLARE @today AS DATETIME
SET @today = GETDATE()
PRINT CAST( FLOOR(CAST(@today AS REAL)) - DATEPART(WEEKDAY, @today) + 2 AS DATETIME)

 


-- Method Three --------------------------------------------------
DECLARE @today AS DATETIME
SET @today = GETDATE()
PRINT CAST( DATEDIFF(DAY, -2, @today) - DATEPART(WEEKDAY, @today) AS DATETIME)

 


-- Method Four --------------------------------------------------

-- This one is not always work.
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE())/7*7, 0)


-- Method Five --------------------------------------------------
SET DATEFIRST 1
SELECT CAST(@date AS DATETIME) - DATEPART(WEEKDAY, CAST(@date AS DATETIME)) + 1

----------------------------------------------------------------
----------------------------------------------------------------

-- determine the first monday of the month
DECLARE @value AS INT
SET @value = FLOOR(CAST(GETDATE() - DATEPART(d, GETDATE()) AS FLOAT)) + 7
SELECT CAST(@value - (@value % 7) AS DATETIME)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s