Improve company productivity with a Business Account.Sign Up

x
?
Solved

Last  Monday of Last Year

Posted on 2013-01-14
5
Medium Priority
?
306 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 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

595 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