CODEWITHSUNDEEP
sql-server-2005selectdateadddatepart
Hani Honey
Hani Honey

Reputation: 2131

Select records from start of month to current date

I'm trying to select records that were added to the database between the start of the current month and the current day - I more or less know how to get records from the current day, and within a specific time period - but how do I get it so it starts from the beginning of the current calendar month?

Upvotes: 1

Views: 7422

Answers (5)

Fox
Fox

Reputation: 417

WHERE DateToCheck > LAST_DAY(CURDATE()-INTERVAL 1 MONTH))

http://www.sqlfiddle.com/#!2/3d673/2/0

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select *
from YourTable
where DateCol >= dateadd(month, datediff(month, 0, getdate()), 0)

Upvotes: 2

Curtis
Curtis

Reputation: 103338

WHERE YEAR([Date])=YEAR(GETDATE())
AND MONTH([Date])=MONTH(GETDATE())
AND DAY([Date])<=DAY(GETDATE())

Upvotes: 2

Jack Marchetti
Jack Marchetti

Reputation: 15754

Basically what you're doing here is Getting the Month, appending '/01' and then appending the year. Casting it as a string to handle the appending, then casting it as a DateTime.

So it's a little bit more involved than doing any sort of date math, but it's readable I think.

DECLARE @firstOfMonth DATETIME
SET @firstOfMonth = CAST(CAST(DATEPART(mm, GetDate()) as varchar) + '/01/' + cast(DATEPART(yyyy, Getdate()) as varchar) as datetime)

WHERE DateToCheck BETWEEN @firstOfMonth and GetDate()

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

DECLARE @sm DATETIME;
SET @sm = DATEADD(DAY, 1-DAY(GETDATE()), DATEDIFF(DAY, 0, GETDATE()));

SELECT columns 
    FROM dbo.foo 
    WHERE datetime_column >= @sm;

Upvotes: 5

Related Questions