SQL Server substring to date in a where caluse

0
votes

I'm attempting to take a date contained within a varchar to compare it with getdate() in a where clause. The varchar variable always looks like this:

Last seen: MM/DD/YY

Some sample data:

Last Seen: 07/12/16
Last Seen: 08/01/16
Last Seen: 07/22/16
NULL
NULL
NULL
NULL
Last Seen: 07/28/16

Converting a varchar to datetime and finding the days difference as below works:

datediff(day,CAST(substring(CA_NOTE, 12, 8) as datetime), getdate()) as dayspassed

The problem is, when I stick this coding in the where clause to compare the date to getdate() I keep getting the same error.

where datediff(day,CAST(substring(CA_NOTE, 12, 8) as datetime), getdate()) > 90

Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string.

I'm running SQL Server 2014 Management Studio.

Edited to take comments into account

destination-data's suggestion of using TRY_PARSE worked! Thanks everyone.

2
sql-serversubstringwhere
can you give us some sample data ? - GuidoG
Thank you for the comments. datediff(day,CAST(substring(CA_NOTE, 12, 8) as datetime), getdate()) > 90 gives the same error message as before. - Adam
...which means you now have a different question. This new version looks ok, so I'll flip back to @GuidoG and his request to show some sample data that we can work with. - Philip Kelley
Try using TRY_PARSE instead. If it returns any nulls the row could not be converted. - David Rushton
destination-data, this worked. Thank you! - Adam

2 Answers

1
votes

SQL does not know what to do with getdate()-90 -- subtract 90 days? 90 minutes? 90 years?

You need to use a function such as dateadd, e.g.

where convert(varchar(30),cast(substring(CA_NOTE, 12, 8) as datetime),102) < dateadd(dd, -90, getdate())
1
votes

The problem is that you're still converting it to a VARCHAR. Try this instead:

CAST(substring(CA_NOTE, 12, 8) as datetime)