Specific day of current month and current year from date time SQL Server 2005

Question

Hello everyone i want search data from invoices and client by today date I'm using DateDiff() GETDATE() functions for example two tables

1 Client

 - ID   int
 - Name Varcher

2 Invoice

 - ID int
 - ClientID int
 - date  Datetime
 - Total  money

query

 Select * from client c 
 inner join invoice i on c.id = i.ClientID 
 WHERE DateDiff(dd, i.date, getdate()) = 0

I want select query by specific day of current month and current year from date time if the current month is 08 and current year 2010 i want write any day of month Thanks every one help me

Answer 1

here is one way which will also be able to use an index

where i.date >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)
and i.date < DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 1)

Answer 2

The simplest way to select records from a specific day in the current month and year is to declare a datetime variable assigned to the specified day, month and year, and replace getdate() in your query with the variable - like so:

declare @date datetime 

select @date = '10-Aug-2010'

Select * from client c 
inner join invoice i on c.id = i.ClientID 
WHERE DateDiff(dd, i.date, @date) = 0

EDIT: To run a query for a specified day of the month in the current month, try the following:

declare @day integer

select @day = 10

Select * from client c 
inner join invoice i on c.id = i.ClientID 
WHERE DateDiff(dd, i.date, dateadd(dd,@day-datepart(dd,getdate()),getdate())) = 0