How to filter the column by date
Using SQL Server 2000
Id date --- ---------- 001 23/01/2012 002 25/01/2012 003 004 01/02/2012
From table1, I want to display id and date is null or date is equal to current month..
For example current month is 02/2012 means, I want to display the id 003 and 004, because 003 date is null, 004 date is equal to current month date...
if current month is 03/2012, 004 date should not display
How to do this in SQL Server?
Id date 003 004 01/02/2012
It's important to make your query sargable so that an index on your date column can be used. Thus a where clause that is of the form function(date) (where function is year, month or datepart) is bad since SQL Server can't use an index on date.
Instead you want to frame your query so that it's of the form
select * from table1 where (date is null) or (date between [STARTOFMONTH] and [ENDOFMONTH])
DateTime columns have, as their name implies, a TIME component. So between is tricky since you want right up until midnight on the end of the month, but not quite midnight leading in to the following month... Some people try to hack around this by subtracting 3 milliseconds from the end of the month. That's bad and not future proof.
It's better to make your query look like
select * from table1 where (date is null) or ( (date >= [STARTOFMONTH] and date < [STARTOFNEXTMONTH]) )
How to get those values (and be SQL 2000 compatible...)? One way, off the top of my head, is
cast( floor( cast(dateAdd(d, -1 * day(getDate()) + 1, getDate()) as float ) ) as datetime )
and the start of the following month is obtained by adding one month to that. This gets the current date, subtracts the number of days elapsed in the current month and then adds 1. We then have a date/time equal to the first day of the month but still with a time component. A cast to float, floor and then cast back to datetime fixes that.
This could be put into a scalar or inline-table UDF, but in the interest of brevity (long answer already!), your where clause is
select * from table1 where (date is null) OR ( ( date >= cast( floor( cast(dateAdd(d, -1 * day(getDate()) + 1, getDate()) as float ) ) as datetime ) ) AND ( date < dateAdd(d, 1, cast( floor( cast(dateAdd(d, -1 * day(getDate()) + 1, getDate()) as float ) ) as datetime ) ) ) )
Hope that helps!
Select * from table1 where date is null or datepart(mm,getdate()) = datepart(mm,date)
you can check using is null and Year ,Month functions
select Id from Table1 where date is null or (YEAR(GETDATE()) == Year(date) and Month(GETDATE()) == Month(date))
Try this way:-
SELECT * FROM table1 WHERE date IS NULL OR (Year(currentDate) = Year(date) AND Month(currentDate) = Month(date))
SELECT * FROM Table1 WHERE (1=1) AND ( MONTH(CURRENT_TIMESTAMP) = MONTH([date]) AND YEAR(CURRENT_TIMESTAMP) = YEAR([date]) ) OR ( [date] IS NULL )