Solved

Last Monday of Last Year

Posted on 2013-01-14

1 solution

293 Views

Last Modified: 2013-01-15

SQL 2008

I am looking at developing a query where I need to use the last Monday of last year, and subsequently going forward every year

In this case it would be

select *
from etc.
where date >= '2012-12-31 00:00:00.000'

Can anybody help?

Comment

Question by:halifaxman

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 74

Expert Comment

by:sdstuber

ID: 387746082013-01-14

dateadd(dd,isnull(nullif((7- datepart(dw,CONVERT(DATETIME,'24/DEC/' + convert(varchar,DATEPART(yyyy,getdate())-1)))+ 2) % 7 ,0),7), CONVERT(DATETIME,'24/DEC/' + convert(varchar,DATEPART(yyyy,getdate())-1)))

LVL 4

Expert Comment

by:parthmalhan

ID: 387746112013-01-14

To Find Last Any Day of last Year

DECLARE @Date datetime=GETDATE()-DATEPART(dw,GETDATE())+6
SELECT      DATEADD(wk,DATEPART(wk,@Date)*-1,@Date)

OR

SELECT      DATEADD(wk,DATEPART(wk,GETDATE()-DATEPART(dw,GETDATE())+6)*-1,GETDATE()-DATEPART(dw,GETDATE())+6)

Where 6 is the Day of Week like below

1      :      Sunday
2      :      Monday
3      :      Tuesday
4      :      Wednesday
5      :      Thrusday
6      :      Friday
7      :      Saturday

LVL 12

Expert Comment

by:Saurabh Bhadauria

ID: 387746182013-01-14

set Datefirst 1

declare @date datetime

set @date=GETDATE()
SELECT dateadd(day, 1-datepart(dw,DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@date),0)))) ,
DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@date),0))))

LVL 35

Expert Comment

by:YZlat

ID: 387752562013-01-14

CREATE FUNCTION dbo.getLastMondayOfYear
(@yr VARCHAR(4))
RETURNS DATETIME
AS
BEGIN
DECLARE @firstDayOfMonth DATETIME
SET @firstDayOfMonth='12/01/' + @yr 
DECLARE @dayWeek int
SET @dayWeek = DATEPART(DW,@firstDayOfMonth) 
DECLARE @count int
SET @count = ((10 - @dayWeek) % 7) + 27
DECLARE @outputDate datetime
SET @outputdate = CONVERT(VARCHAR(10),DATEADD(day,@count,@firstDayOfMonth),101)
RETURN @outputdate
END

Open in new window

then call it as

select dbo.getLastMondayOfYear('2013')

Open in new window

LVL 69

Accepted Solution

by:Scott Pletcher

Scott Pletcher earned 500 total points

ID: 387755772013-01-14

SELECT date, DATEADD(DAY, DATEDIFF(DAY, 0, CAST(YEAR(GETDATE()) - 1 AS char(4)) + '1231') / 7 * 7, 0)

That code works with any date and language settings =
SET DATEFIRST <any> and
SET LANGUAGE <any>

Some other sample dates and results:

SET DATEFIRST 4 --just an example, to test

SELECT date, DATEADD(DAY, DATEDIFF(DAY, 0, CAST(YEAR(date) - 1 AS char(4)) + '1231') / 7 * 7, 0)
FROM (
SELECT '20130114' AS date UNION ALL
SELECT '20140114' UNION ALL
SELECT '20150114' UNION ALL
SELECT '20120114'
) AS test_dates

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Lone Date Ranger :: there is just one right way

Article by: PortletPaul

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …

Query Syntax

Batch File to Perform Backup of MSSQL Database

Article by: Brian

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…

Network monitoring: how to automate fixing common network or server issues?

Video by: AdRem

Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…