Change Query to only show 3 years of older dates

Question

Post reply

Change Query to only show 3 years of older dates

rtrice81

SSC Rookie

Points: 48
More actions
May 14, 2015 at 8:56 am

#303865

I am working with this query, however I would like it to only show dates that are 3 years or older.
What is the correct way to go about doing this?
Here is the query. Thanks in advance
SELECT
Nodes.NodeID AS NodeID
, Caption AS NodeName
, Nodes.Modal AS Modal
, CustomPollerAssignment.AssignmentName AS Assignment
, CustomPollers.UniqueName AS PollerName
, CAST(CustomPollerStatus.Status AS Date) AS ReplacementDate
, ISDATE(CustomPollerStatus.Status) AS ISDATEValid
FROM CustomPollerStatus
JOIN CustomPollerAssignment ON CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
JOIN CustomPollers ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID
JOIN Nodes ON (Nodes.NodeId = CustomPollerAssignment.NodeId)
WHERE (CustomPollers.UniqueName = 'APCupsBasicBatteryLastReplaceDate') AND (ISDATE(CustomPollerStatus.Status) = 1)
ORDER BY 6 DESC

Viewing 4 posts - 1 through 3 (of 3 total)

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

I don't know which column is your datecol but something like this:

WHERE <datecolumn> <= DATEADD(YEAR,-3,getdate())

We would also have to determine exactly what you mean by "three years old" e.g. leap years considered? Three years to what time it is now or three years to the day? - stuff like that...

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

rtrice81 SSC Rookie Points: 48 More actions · Answer 2

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.