3

I have a Ticket_Date column that is in the format YYYY-MM-DD HH:MI:SS

I want to check if the Ticket_date is in the current month.

So far I have :

 Ticket_date >= '2015-04-01' and Ticket_date < '2015-04-30'

but I need it to work with the current month rather then hardcoded

Amit Chigadani
  • 28,482
  • 13
  • 80
  • 98
bigdowg
  • 379
  • 3
  • 5
  • 19

4 Answers4

15
 YEAR(Ticket_date) = YEAR(getdate()) and 
MONTH(Ticket_date) = MONTH(getdate())

but i would also save current date in variable to guarantee what result of getdate() doesn't change when executing long query at midnight

declare @today datetime = getdate()
...
 YEAR(Ticket_date) = YEAR(@today) and 
MONTH(Ticket_date) = MONTH(@today)
ASh
  • 34,632
  • 9
  • 60
  • 82
5
MONTH(Ticket_date) = MONTH(GETDATE()) AND YEAR(Ticket_date) = YEAR(GETDATE())
knkarthick24
  • 3,106
  • 15
  • 21
3

How about this?

where ticket_date >= cast(getdate() - day(getdate) + 1 as date) and
      ticket_date < dateadd(month, 1, cast(getdate() - day(getdate) + 1 as date) )

This formulation has no functions on ticket_date, so SQL Server can use an index on the column, if appropriate.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • when i trigger this in `2019-01-22` it will generate condition `>= '2019-01-01' and < '2019-02-01'` which will returns also records for January month, not only February as current month. But you have good point with the index usage. – Muflix Jan 22 '19 at 10:06
  • @Muflix . . . The question is: "I want to check if the Ticket_date is in the current month." This query is the most efficient way to achieve that result. – Gordon Linoff Jan 22 '19 at 12:43
  • you are right. It will not return February which was my mistake. – Muflix Jan 22 '19 at 12:58
0

What about (Server 2008 R2+)

-- first day of current month
Ticket_date  >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
-- last day of current month 
and Ticket_date <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

More examples here How can I select the first day of a month in SQL?

  • Why do you omit the = in Ticket_date < '2015-04-30' ?
Muflix
  • 6,192
  • 17
  • 77
  • 153