Comparing Dates, but only the Month/Year parts. 1

[MichaelaLee]

Hi Everyone,
I'm working on a UDF that will tell me if a date falls within a Date range. The catch is I can't look at the Day part of the date as a comparision. In other words, If I have the following:
LookUpDate = 6/25/2004
StartDate = 2/1/2004
EndDate = 2/5/2005
The UDF should only look at it like this:
LooupDate = 6/2004
StartDate = 2/2004
EndDate = 2/2005
Then see if the LookUpDate falls between the other dates.

Here is some test code I'm working with:
Use Attendance
go
Declare @FromDate smalldatetime
Declare @ToDate smalldatetime
Declare @FP smalldatetime

Select @FP = '6/25/2004'
Select @FromDate = '2/01/2004'
Select @ToDate = '2/20/2005'

if datepart(m, @FP) >= datepart(m, @FromDate) and datepart(m, @FP) <= datepart(m, @ToDate)
begin
if datepart(yyyy, @FP) >= datepart(yyyy, @FromDate) and datepart(yyyy, @FP) <= datepart(yyyy, @ToDate)
Print '1'
Else
Print '0'
End
Else
Print '0'

This code is still in the works, because if don't work right with the dates given above. I'm working on it know, but I wanted to see if others have needed something like this before. If so, How did you all handle it. Thanks for any tips or advice.
Michael Lee

 

[SQLSister]

Personally, I would simply insist that the values for start and end dates be the first of the month. For the end date use 2/1/2005 if you want to check dates only upto the first of Feb or 3/1/2005 if you want Feb dates to be included.

If you can't control the input directly, just change those two dates to the first of the month programmically. Then you can Select * from table1 where @lookupdate > @startDate and < @EndDate

The easiest way to control the start and end dates is to have a form where the user picks there that takes the month and year and converts it to the first of the month for use within the query.





Questions about posting. See faq183-874

 

[vongrunt]

print case when datediff(mm, 0, @FP) between datediff(mm, 0, @FromDate) and datediff(mm, 0, @ToDate) then '1' else '0' end

One of these days I'll have to buy donutman a biiiig donut .

 

[dbomrrsm]

Very neat Vongrunt - very neat indeed - I'd been pondering that one for a while.

Well done - have a star on me.

[blue]DBomrrsm[/blue]

 

[ESquared]

ooooh nice!

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[SQLSister]

I love it when people think out of the box. Shiny, vongrunt.

Questions about posting. See faq183-874