query problem

hi,

I need this as a filter criteria in my where clause for my select statement,

I need to set

Year1 = current year and

Year2 = currentyear-1

when current date's [month-day] > july30th

current year is in the form of 07 or 06 or 05

i.e

Year1 = 07

Year2 = 06

when current date's[ month-day] < july01

Year1 = current year - 1 and

Year2 = currentyear-2

Year1 = 06

Year2 = 05

I know I can use datepart, but current date from getdate() function gives

2007-10-07-hh.min.sec

and I need to compare this with

july 10th or june30th at any point of a year

also

set Year1

Year2 in the form 07 or 06 or 05

thnx

I suggest that this, and many date related queries you now have or will have in the future will be so much easier if you were to explore using a Calendar table. See this source.

Seems like you need to compare a date against Jul 01, so DATEDIFF() 'could' help.

Here is one simple use of DATEDIFF():

DECLARE
@.DateToVerify datetime,
@.FYear datetime

SELECT
@.DateToVerify = getdate(),
@.FYear = dateadd( day, 181, dateadd( year, datediff( year, 0, getdate() ), 0 ))

SELECT CASE
WHEN datediff( day, @.FYear, @.DateToVerify ) >= 0 THEN '07'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-365) THEN '06'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-730) THEN '05'
ELSE '04'
END

Most likely there are many ways to 'skin this cat', as the saying goes. But a Calendar table is often the most robust method of handling date span/range issues.