Date Range Filter 1

[titans4771]

I have a report where I need to calculate units for the year:

date between '2009-01-01' and '2009-12-01'

How can I write the dates so it automatically pick the current year without having to change it manually?

Thanks!!

 

[bborissov]

WHERE YEAR(DateField) = YEAR(GETDATE())

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

declar @StartDate datetime, @EndDate datetime

set @StartDate = cast(year(Getdate()) as char(4)) + '0101'
set @EndDate = cast(year(Getdate()) as char(4)) + '1231' -- careful here - do we need to include last date info?

May be instead

set @StartDate = cast(year(Getdate()) as char(4)) + '0101'
set @EndDate = dateadd(year,1,@StartDate)

select * from myTable where DateField >=@StartDate and DateField<@EndDate

 

[kermit01de]

Thanks Borislav,

that just helped me in another date/time question!

Thanks for the kick into the right direction.

Mirko

--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[markros]

Borislav's response on this question is not a good one, unfortunately. No offense, Boris, you know how much I like you

Check this

http://sqlblog.com/blogs/aaron_bert...s-to-kick-mishandling-date-range-queries.aspx

and you'll see why I made my suggestion.

 

[markros]

BTW, in my suggestion I forgot to convert @StartDate and @EndDate to datetime - not sure why I forgot.

 

[bborissov]

Hmm,
No offense taken
I never thought I am the last instance and always give the right answers. Sometimes I wrote the answers directly here, because I have no time to test them or think more carefully about them.
Always like when someone corrects me.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.