Improve company productivity with a Business Account.Sign Up

Solved

Last Monday of Last Year

Posted on 2013-01-14

1 solution

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?

Comment

Question by:halifaxman

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 70

Accepted Solution

by:Scott Pletcher

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

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 Already a member? Login.

How to replicate SQL 2008 database to SQL 2014 by using Initialize from Backup.

Article by: lcohan

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…

How to Attach SQL Database Which is Not Properly Detached

Article by: Bharat

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.

Software
Query Syntax
SQL
Databases
Microsoft SQL Server 2008
Microsoft SQL Server, Microsoft SQL Server 2005, *T-SQL

Synchronize List Box in Access using SQL and VBA

Video by: crystal

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…

How to Bring Back the SQL Database from Suspect Mode to Normal Mode?

Video by: Bharat

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.