Get month from date time now month, Get year from date time now year and write specific day

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

when i run query the getdate() it is function to get date from date time now the date content current month and current year and current day when i run query today the date is 08-23-2010, when i run query tomorrow the date is 08-24-2010.

Q - the query do something equal date i want get month from date time now month, get year from date time now year and write specific day only.

Answer 1

I do not understand your question clearly, but as far as I understand you want to have a time difference other than 0 days. You would then have to use other dateparts (first argument to DateDiff()) See the MS documentation of DateDiff for details, or the DateAdd function.

Furthermore, maybe you want to use

i.date between DateAdd(dd, -10, getDate) and getDate()

(which would show everything between ten days ago and today).

Answer 2

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