[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Last  Monday of Last Year

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

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

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…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

640 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