Solved

Last  Monday of Last Year

Posted on 2013-01-14
5
286 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
5 Comments
 
LVL 73

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:
ScottPletcher 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now