CODEWITHSUNDEEP
sqlsql-serverdatetime
99LittleBugsInTheCode
99LittleBugsInTheCode

Reputation: 85

Filter results after date SQL Server

I need to filter my query to extract the results between 25th of the previous month and 25th of the current month. The SP receives a date as parameter, which I am using to extract the current month.I tried to make some casting but I can't figure how to deal with the January month when the last month has a different year(there must be a more efficient way also)

@Date smalldatetime --received as parameter
select *
from mytable
where mytable.mydate between '25/'+cast(MONTH(@date)-1 as varchar(2))+'/'+cast(YEAR(@date) as varchar(4)) and '25/'+cast(MONTH(@date) as varchar(2))+'/'+cast(YEAR(@date) as varchar(4))

Upvotes: 1

Views: 93

Answers (2)

Nate Reynolds
Nate Reynolds

Reputation: 137

You don't need to do any casting. SQL Server's datetime functions are quite versatile. Try this:

SELECT    *
FROM       mytable
WHERE    mytable.mydate BETWEEN DATEADD(DAY, 25, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 1, 0)) AND DATEADD(DAY, 25, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would just do:

select t.*
from t
where mydate >= dateadd(month, -1, datefromparts(year(getdate()), month(getdate()), 25)) and
      mydate < datefromparts(year(getdate()), month(getdate()), 25)

I would use datefromparts() rather than trying to construct a date string for this purpose.

Upvotes: 3

Related Questions