Getting All the record of particular month - Building SQL Query

Question

Hi all, I need some help to build SQL Query. I have table having data like:

ID  Date        Name
1   1/1/2009    a
2   1/2/2009    b
3   1/3/2009    c

I need to get result something like...

1    1/1/2009    a
2   1/2/2009    b
3   1/3/2009    c
4   1/4/2009    Null
5   1/5/2009    Null
6   1/6/2009    Null
7   1/7/2009    Null
8   1/8/2009    Null
............................
............................
............................
30  1/30/2009   Null
31  1/31/2009   Null

I want query something like..

Select * from tbl **where month(Date)=1 AND year(Date)=2010**

Above is not completed query.

I need to get all the record of particular month, even if some date missing..

I guess there must be equi Join in the query, I am trying to build this query using Equi join

Thanks

Answer 1

BIG EDIT

Now understand the OPs question.

Use a common table expression and a left join to get this effect.

DECLARE @FirstDay DATETIME;

-- Set start time
SELECT @FirstDay = '2009-01-01';

WITH Days AS 
(
  SELECT @FirstDay as CalendarDay
  UNION ALL
  SELECT DATEADD(d, 1, CalendarDay) as CalendarDay
  FROM Days
  WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, 1, @FirstDay)
)
SELECT DATEPART(d,d.CalendarDay), **t.date should be (d.CalendarDay)**, t.Name  FROM Days d
LEFT JOIN tbl t
ON
  d.CalendarDay = t.Date
ORDER BY 
  d.CalendarDay;

Left this original answer at bottom

You need DATEPART, sir.

SELECT * FROM tbl WHERE DATEPART(m,Date) = 1

If you want to choose month and year, then you can use DATEPART twice or go for a range.

SELECT * FROM tbl WHERE DATEPART(m,Date) = 1 AND DATEPART(yyyy,Date) = 2009

Range :-

SELECT * FROM tbl WHERE Date >= '2009-01-01' AND Date < '2009-02-01'

See this link for more info on DATEPART.

http://msdn.microsoft.com/en-us/library/ms174420.aspx

Answer 2

You can use less or equal to.

Like so:

select * from tbl where date > '2009-01-01' and date < '2009-02-01'

However, it is unclear if you want month 1 from all years?

You can check more examples and functions on "Date and Time Functions" from MSDN

Answer 3

Create a temporary table containing all days of that certain month,

Do left outer join between that table and your data table on tempTable.month = #month.

now you have a big table with all days of the desired month and all the records matching the proper dates + empty records for those dates who have no data.

i hope that's what you want.