CONVERT(int, (datepart(month, @search)), (datepart(day, @search)), DateAdd(year, Years.Year - (datepart(year, @search)))

Posted by MyHeadHurts on Stack Overflow See other posts from Stack Overflow or by MyHeadHurts
Published on 2011-01-07T19:41:30Z Indexed on 2011/01/07 19:53 UTC
Read the original article Hit count: 587

Filed under:
|

In the query the top part is getting all the years that will run in the stored procedure. Works fine

But at first i just wanted to run the queries for yesterdays date for all the years, but now i realized i want the user to select a date that will be in a parameter @search

Booked <= CONVERT(int,DateAdd(year, Years.Year - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 1)))

this should be easy because normally it would just be

Booked <= CONVERT(int,@search)

but the problem is i want to do something like a

Booked <= CONVERT(int, (datepart(month, @search)), (datepart(day, @search)), DateAdd(year, Years.Year  - (datepart(year, @search)))

would something like that work i dont need to worry about subtracting days but i still need to worry about the years

    WITH Years AS (
        SELECT DATEPART(year, GETDATE()) [Year]
        UNION ALL
        SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet 
    ),
q_00 as (
select
      DIVISION
     , DYYYY
     , sum(PARTY)         as asofPAX        
    , sum(APRICE)     as asofSales        
from dbo.B101BookingsDetails 
INNER JOIN Years                     ON B101BookingsDetails.DYYYY = Years.Year
where Booked <= CONVERT(int,DateAdd(year, Years.Year  - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 1)))
  and DYYYY = Years.Year 
group by DIVISION, DYYYY, years.year
having  DYYYY = years.year
),

© Stack Overflow or respective owner

Related posts about sql

Related posts about stored-procedures