0

I am selecting fields like client id, name, and service date. I am trying to write in my where clause, that every day I run my query it will capture a date range of the first of the current month to the current day.

Chris
  • 323
  • 5
  • 21
  • http://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql The rest should be easy. Hint `Date between DATEADD(month, DATEDIFF(month, 0, @mydate), 0) and @mydate` – austin wernli Jun 27 '16 at 19:31

2 Answers2

2

One method would be to compare the month and the year:

where year(col) = year(getdate()) and
      month(col) = month(getdate()) and
      day(col) <= day(getdate())  -- this is optional, if there is no future data

Another method would be to compare to the beginning of the month. I would approach this as:

where col >= cast(dateadd(day, 1 - day(getdate), getdate()) as date)

(This assumes there is no future data in the table.)

or, better yet:

where col >= datefromparts(year(getdate()), month(getdate()), 1)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
  Select * 
    From YourTable 
    where SomeDateField bewteen cast(DateAdd(DD,-Day(GetDate())+1,GetDate()) as Date)  and cast(getDate() as Date)
      and ...

Edit: I used the BETWEEN to trap possible future date/scheduled events

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66