Solved

Last  Monday of Last Year

Posted on 2013-01-14
5
291 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?
0
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
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38774608
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)))
0
 
LVL 4

Expert Comment

by:parthmalhan
ID: 38774611
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
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38774618
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))))
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38775256
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

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38775577
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
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question