Your technology certification is waiting. Enroll in Cloud Class ®
-- first of last 3 months (?)
dateadd(month,-3,cast(getdate() as date))
as [3 months ago]
as [day of month 3 months ago]
, dateadd(day,-datepart(day,dateadd(month,-3,getdate()))+1, dateadd(month,-3,cast(getdate() as date)) )
as [first of month 3 months ago]
-- first of last 3 months (? a "quarter")
dateadd(quarter,datediff(quarter,0,getdate()),0) as [First of this quarter]
, dateadd(quarter,datediff(quarter,0,getdate())+1,-1) as [Last day of this quarter]
Open in new window
sql query last six months
I need help writing a query
is last 6 Mondays
last 4 days
first of last 3 months
-- set number of weeks
declare @numWeeks as int
set @numWeeks = 6
-- next get recent Monday
declare @BeginsAt as datetime
-- truncate time from getdate()
set @BeginsAt = dateadd(day, datediff(day,0, getdate() ), 0)
-- get Monday using modulus
set @BeginsAt = dateadd(day,(-datediff(day,0,@BeginsAt) % 7),@BeginsAt)
;with Ranger (id, StartAt)
/* recursively build CTE of Mondays */
select 1 as id, @BeginsAt
select (id + 1) , dateadd(day,-7,StartAt)
where dateadd(day,-7,StartAt) >= dateadd(week,-@numWeeks+1,@BeginsAt)
id, datename(weekday,StartAt), StartAt
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.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.