Post reply

Current year and Previous year values in same report

  • December 28, 2010 at 4:22 am

    #1266674

    Sorry New Commer for the previous code

    Please use the following code

    It will give u the desired result.

    DECLARE @CYBeginDate smalldatetime, @CYEndDate smalldatetime,

    @LYBeginDate smalldatetime, @LYEndDate smalldatetime

    Declare @para datetime

    set @para = '2010-04-01'

    select @CYBeginDate = dateadd(year, datepart(year, @para) - (case when datepart(month, @para) <= 3 then 1901 else 1900 end), '1900-04-01')

    select @CYEndDate = dateadd(day, -1, dateadd(month, 12, @CYBeginDate))

    select @LYBeginDate = dateadd(year, -1, @CYBeginDate)

    select @LYEndDate = dateadd(year, -1, @CYEndDate)

    SELECT CONVERT(VARCHAR(10), @CYBeginDate, 103) AS 'CurrentYearBeginDate',

    CONVERT(VARCHAR(10), @CYEndDate, 103) AS 'CurrentYearEndDate',

    CONVERT(VARCHAR(10), @LYBeginDate, 103) AS 'LastYearBeginDate',

    CONVERT(VARCHAR(10), @LYEndDate, 103) AS 'LastYearEndDate'

  • December 28, 2010 at 4:28 am

    #1266677

    Hi New Commer

    I agree with kingston for not using varchar for date.

    And try to minimize datatype conversion

    If ur date columns are same as the dafault format of sql server then u dont even have to use the convert as i used it in my code in the last statement.

    Hope i have helped u

    Best of luck

  • January 3, 2011 at 10:38 pm

    #1268744

    Kingston Hat off !

    only wen i running the query by now , i come to know about your point and came to search this thread..

    Thanks a LOT for you ...

    And Abhijeet Thanks for your Updated query !!! Of course u did help me !!!Thank you !!

    HAPPY NEW YEAR 2011 !!!

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply