SQLTeam.com | Weblogs | Forums

Query list values for current month

Hello!
I have a table with a date field
I have a query where I filter the results according to one filed but I would also like to list only records for current month.
I'm trying to use one of these:

= 'MONTH(DataEntradaProcesso) = MONTH(GetDate())'
= 'MONTH(DataEntradaProcesso) = MONTH(CURDATE())'

But I get the error:

"your entry cannot be converted to a valid date time value"

Also tried to create a new date column, just to check if there was something I had changed that I didn't remember, but I get the same error...
Can anyone give me a hand?
Thanks in advance.

hi

i tried to test ... i am using MicroSoft SQL Server T-SQL
everything looks OK ...

r u using some other software ????
or
is the mistake somewhere else in the query and its giving error

please click arrow mark to the left for "drop create data"
drop table #data 
go 

create table #data
(
[date] date 
) 
go 

insert into #data select '2019-09-10'
insert into #data select '2018-11-02'
go 

select MONTH([date]) from #data
go 

select MONTH(getdate()) 
go

1 Like

I'm using Sql server management studio express.
Will test it with your data.
I've already test it on the sabe query , not using the other condition for filtering and get the same error...

If you want only the current month - I am assuming that to be month to date. It is much better to query on actual date/time values instead of using functions.

The problem with using these functions is that you also have to consider the year - and it isn't necessary:

SELECT ...
FROM ...
WHERE datefield >= dateadd(month, datediff(month, 0, getdate()), 0)
AND datefield < cast(getdate() as date)

The above will include everything from the first of this month - through the end of the day yesterday. If you want everything up to current point in time, then it depends on your date field. If the date field can contain future dates - then:

AND datefield < getdate()

If your date field cannot contain future dates - then you can just drop that portion and the query will return all available rows at the time it is executed.

1 Like

So, no errors with this option. Thank you for that! :slight_smile:
This view is for us to know if we achieve or not the monthly goal, so it can't be a count till yesterday and can't repeat values for months from different years. Using your first option, the records that were inserted today were not showing and with the second option they did show.
Will it return to "0" on the first day of the next month? 'Cause that's what we intend.
And thank you once again!

This code dateadd(month, datediff(month, 0, getdate()), 0) calculates the first of the month for the given date. In this case - the given date is current date so it will always calculate the first of this month.

1 Like

What about if I want to list:

(Every record has a stage column)

  • All records for all stages AND
  • if stage = "in court", just show the ones with the submission date column, from this month (what you suggested and that works).

I know this must be simple but I can't get my head around on how to join the 2 conditions

I am not sure what you are looking for - it would be easier if you provided sample data and expected results as create table (sample test table - can be temp table or variable) and insert statements to create the test data (as outlined by @harishgg1 in his response).

Lets assume this criteria is in addition to the other criteria - if so, then you need an OR:

SELECT ...
FROM ...
WHERE (
      datefield >= dateadd(month, datediff(month, 0, getdate()), 0)
  AND datefield < cast(getdate() as date)
      )
   OR (
      Stage = 'in court'
  AND SubmissionDate >= dateadd(month, datediff(month, 0, getdate()), 0)
  AND SubmissionDate <  cast(getdate() AS date)
      )
1 Like

Thank you, I just needed the idea on how to get both conditions and you helped me.
I've used the logic: all records where the Submission date is null (and that shows all the other records) OR where Stage field = something, show the one that have a submission date and then show the ones only for the current month

WHERE (dbo.Processos.DataEntradaProcesso >= DateAdd(month, DateDiff(month, 0,
GetDate()), 0) AND dbo.Processos.DataEntradaProcesso < GetDate())
OR
(dbo.Processos.DataEntradaProcesso IS NULL)