Home Forums SQL Server 2008 SQL Server Newbies Change Query to only show 3 years of older dates RE: Change Query to only show 3 years of older dates

  • May 14, 2015 at 10:06 am

    #1797917

    rtrice81 (5/14/2015)

    It would be 3 years to the day the query is ran, my date column is CAST(CustomPollersStatus.Status AS Date)

    I updated the WHERE to this

    WHERE (CustomPollers.UniqueName = 'APCupsBasicBatteryLastReplaceDate') AND (CAST(CustomPollerStatus.Status AS Date) > DATEADD(year,-3,GETDATE())) AND (ISDATE(CustomPollerStatus.Status) = 1)

    and I am getting this error

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.

    the date is in this format yyyy-mm--dd or 2015-05-14

    Thanks

    PS I am starting to like the coding stuff, lol

    That error is telling you that there is data in the column CustomPollersStatus.Status that can't be converted to a DATE data type. This is a good reason that dates and date/time values should be stored in columns defined with the appropriate date or date/time data type.

    One solution would be to copy the data that meets the isdate(CustomPollersStatus.Status) = 1 criteria to a temporary table and use it in your query.