none
Adding a value to a datetime column caused overflow

    Question

  • Hi im getting the above error in SSRS 2008

    I have a report based on a query similar to the below,

    Select Name, Case when [Posting Date] >=  DATEADD(YEar, -1, @FDate ) and  [Posting Date] <=  DATEADD(YEar, -1, @TDate ) ) Then Value Else 0 end as YesterYear  from Table1

    It was working well in Query Builder but throwing error when executing in Preview.

     

    According to the MSDN link,

    http://msdn.microsoft.com/en-us/library/ms186819.aspx

     

    i modified the query as below,

    Select Name, Case when ((CONVERT(Varchar(20), [Posting Date], 111) >=  DATEADD(YEar, -1, @FDate ) and 

    CONVERT(Varchar(20), [Posting Date], 111) <=  DATEADD(YEar, -1, @TDate ) ) Then Value Else 0 end as YesterYear 

    from Table1

    but still the error is throwing.

     

    Kindly help... Thanks !

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Friday, February 25, 2011 8:46 AM

Answers

  • hi ! The issue solved ...

    The issue got raised due to the fact of default value set in for the parameter as 01/01/1800.

    When the value was changed to 1950 the issue was solved...


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Wednesday, March 02, 2011 10:02 AM

All replies

  • But the expression returns value or 0. What is the datatype of value? datetime?

    select

     

    cast(0 as datetime)

    1900-01-01 00:00:00.000


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Friday, February 25, 2011 8:50 AM
  • But the expression returns value or 0. What is the datatype of value? datetime?

    select

     

    cast(0 as datetime)

    1900-01-01 00:00:00.000


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Friday, February 25, 2011 8:50 AM
  • But the expression returns value or 0. What is the datatype of value? datetime?

    select

     

    cast(0 as datetime)

    1900-01-01 00:00:00.000


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    No datetime, the 'Value' field its numeric.

    Cast(FDate as Datetime) is also throwing error

     

    PLease any idea??

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Friday, February 25, 2011 9:42 AM
  • Hi Any help please ....

    After surfing net, i found the error occurred due to the use of DateAdd function...

     ([Posting Date] >= DATEADD(year, -1, @FDate  ) and [Posting Date] <= DATEADD(year, -1, @TDate )

     

    What if we can use to find the yesteryear day corresponding to current year.??

     

    Please let me know.


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Monday, February 28, 2011 4:50 AM
  • Hi Radhai,

    Generally, if we pass Date/Time report parameters to the query, the return data type of the DATEADD function is the also datetime. Since we compare the posting date to the return values, what’s the date type of the [Posting Date] field?

    Based on my testing, if the [Posting Date] is also datetime, the following works fine

    SELECT     Name,
    CASE
    WHEN [Posting Date] >= DATEADD(YEar, - 1, @FDate) AND [Posting Date] <= DATEADD(YEar, - 1, @TDate)
    THEN Value ELSE 0
    END AS YesterYear
    FROM         Table_1

    However, if the data type of the [Posting Date] field is not datetime, please convert it and test again.

    Thanks,
    Tony Chain


    Tony Chain [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, March 01, 2011 7:26 AM
    Moderator
  • Hi Radhai,

    Generally, if we pass Date/Time report parameters to the query, the return data type of the DATEADD function is the also datetime. Since we compare the posting date to the return values, what’s the date type of the [Posting Date] field?

    Based on my testing, if the [Posting Date] is also datetime, the following works fine

    SELECT     Name,
    CASE
    WHEN [Posting Date] >= DATEADD(YEar, - 1, @FDate) AND [Posting Date] <= DATEADD(YEar, - 1, @TDate)
    THEN Value ELSE 0
    END AS YesterYear
    FROM         Table_1

    However, if the data type of the [Posting Date] field is not datetime, please convert it and test again.

    Thanks,
    Tony Chain


    Tony Chain [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Dear Tony,

    The Posting Date field is of Datetime only and it holds the value like,

    2009-12-31 23:59:59.000

    I tried displaying the DateAdd parameter value DATEADD(YEar, - 1, @TDate),

    and it displays the value as ,

    12/31/2010 12.00.00 AM

    i tried changing again the posting field to datetime field using Cast([Posting Date] as Datetime), but still the throws the error..

    i am very confused...Wondering why the query works in the designer mode but not in preview mode..

     

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Tuesday, March 01, 2011 7:41 AM
  • hi ! The issue solved ...

    The issue got raised due to the fact of default value set in for the parameter as 01/01/1800.

    When the value was changed to 1950 the issue was solved...


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Wednesday, March 02, 2011 10:02 AM