Query (date) help

[Bell1991]

I need to select all records from a table that were insered this year (not previous years).. here is what i have:

DECLARE @CurrentDateTime datetime
set @CurrentDateTime = getDate()
PRINT @CurrentDateTime

DECLARE @GetRecordYear varchar(4)
set @GetRecordYear = datepart(yy, @CurrentDateTime)
PRINT datepart(yy, @CurrentDateTime)
print @GetRecordYear

Select * from tblA
where dtDate < = @CurrentDateTime
where ( datepart(yy, @CurrentDateTime) = @GetRecordYear )

For some reason this is not working..

If i do this:
if (datepart(yy, @CurrentDateTime) = @GetRecordYear )
print 'true'
else
print 'false'

this returns true - so i am not sure what i am doing wrong in my above query...

Any suggestions?

Thanks,
-Bell

 

[vongrunt]

This simply cannot work:

Select * from tblA
where dtDate < = @CurrentDateTime
where ( datepart(yy, @CurrentDateTime) = @GetRecordYear )

Two WHERE clauses... which one you used?


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Bell1991]

woops copied it wrong..

Select * from tblA
where dtDate < = @CurrentDateTime
and (datepart(yy, @CurrentDateTime) = @GetRecordYear

 

[gmmastros]

You have 2 where's, which is a no-no.

Also. datepart returns integer, so declare it as such.

DECLARE @GetRecordYear Integer
set @GetRecordYear = datepart(yy, @CurrentDateTime)

Select * 
from   tblA
where  dtDate < = @CurrentDateTime
       And datepart(yy, @CurrentDateTime) = @GetRecordYear

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

??? of course they are both 2006

set @CurrentDateTime = getDate()--This is 2006
set @GetRecordYear = datepart(yy, @CurrentDateTime) --from the 2006 part

here you are testing for the same

if (datepart(yy, @CurrentDateTime) = @GetRecordYear )

after setting it here set @GetRecordYear = datepart(yy, @CurrentDateTime)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jaylou]

Have you tried

Select * from tblA
where year(dtDate) = Year(getdate())

HTH,
Joe

 

[vongrunt]

Actually you can do:

where dtDate >= @GetRecordYear
and dtDate < dateadd(yy, 1, @getRecordYear)

Also gets the job done - and is SARGable.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Bell1991]

Thanks!