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:
"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.
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
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.
So, no errors with this option. Thank you for that!
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.
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)
)
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)
