Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
Solved
Last Monday of Last Year
Posted on 2013-01-14
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?
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?
[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
Active today
dateadd(dd,isnull(nullif((
To Find Last Any Day of last Year
DECLARE @Date datetime=GETDATE()-DATEPAR
SELECT DATEADD(wk,DATEPART(wk,@Da
OR
SELECT DATEADD(wk,DATEPART(wk,GET
Where 6 is the Day of Week like below
1 : Sunday
2 : Monday
3 : Tuesday
4 : Wednesday
5 : Thrusday
6 : Friday
7 : Saturday
DECLARE @Date datetime=GETDATE()-DATEPAR
SELECT DATEADD(wk,DATEPART(wk,@Da
OR
SELECT DATEADD(wk,DATEPART(wk,GET
Where 6 is the Day of Week like below
1 : Sunday
2 : Monday
3 : Tuesday
4 : Wednesday
5 : Thrusday
6 : Friday
7 : Saturday
set Datefirst 1
declare @date datetime
set @date=GETDATE()
SELECT dateadd(day, 1-datepart(dw,DATEADD(ms,-
DATEADD(ms,-3,DATEADD(yy,0
declare @date datetime
set @date=GETDATE()
SELECT dateadd(day, 1-datepart(dw,DATEADD(ms,-
DATEADD(ms,-3,DATEADD(yy,0
Active 2 days ago
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
then call it as
select dbo.getLastMondayOfYear('2013')
Active today
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
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.
If you are experiencing a similar issue, please ask a related question
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month12 days, 1 hour left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.