CODEWITHSUNDEEP
sqlsql-server
Mark P.
Mark P.

Reputation: 282

SQL - Get data based on months form a dateTime column

With SQL Server, I have a column with a launch date (dateTime). I want to report on everything that is being launched between all of last month (from viewing date) thru all of this month and next month.

So basically a full 3 month period.

What is the best way to write that?

Upvotes: 2

Views: 1689

Answers (3)

sazh
sazh

Reputation: 1832

Are you looking for something like this?

DECLARE
    @StartDate DATETIME,
    @EndDate DATETIME

SELECT
    @StartDate = DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0),
    @EndDate = DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 2, 0)

-- DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0), -- beginning of this month
-- DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0), -- beginning of last month
-- DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0) -- beginning of next month
-- DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 2, 0) -- beginning of two months from now

SELECT
    *
FROM
    [Table]
WHERE
    [LaunchDate] >= @StartDate
AND [LaunchDate] < @EndDate

This will give you all the results starting from the beginning of the previous month and before the beginning of two months from now (a full 3 month range)

Upvotes: 2

berty
berty

Reputation: 2206

Maybe something like SELECT /*what_you_want*/ from launches WHERE lauchDate BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 1, '19000101') AND DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 2, '19000101')

Upvotes: 2

Justin Helgerson
Justin Helgerson

Reputation: 25521

SELECT Foo
FROM Bar
WHERE LaunchDate >= DATEADD(mm, -1, GETDATE())
AND LaunchDate <= DATEADD(mm, 1, GETDATE())

Upvotes: -1

Related Questions