I have a column (notes) that is a string. This field is periodically updated and more information is appended to it. (Not my design). What I need to do is be able to look for the last date that is put into the string and then compare that date to see if it is between two other dates.
For the purposes of this question we can assume 1 table - table1 with 1 column - Notes.
A sample piece of data for the notes column would be...
blurb on 03/23/2004 by Someone blurb on 03/23/2004 by Someone blurb on 09/27/2004 by Someone Blurb on 09/27/2004 by Someone
Of course, I took the actual data out and replaced it with blurb to protect the innocent.
Here's the query...
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = convert(datetime, '10/01/2006',101)
SET @EndDate = convert(datetime, '06/30/2007',101)
select convert(datetime,substring
(notes, CHARINDEX (' on', notes, len(notes)-25)+4, 10),101)
from table1
where notes <> ''
and substring(notes, CHARINDEX (' on', notes, len(notes)-25)+4, 1) in ('0','1')
and convert(datetime,substring
(notes, CHARINDEX (' on', notes, len(notes)-25)+4, 10),101)
BETWEEN @StartDate AND @EndDate
I get...
Msg 241, Level 16, State 1, Line 8
Conversion failed when converting datetime from character string.
If I remove the between line...
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = convert(datetime, '10/01/2006',101)
SET @EndDate = convert(datetime, '06/30/2007',101)
select convert(datetime,substring
(notes, CHARINDEX (' on', notes, len(notes)-25)+4, 10),101)
from table1
where notes <> ''
and substring(notes, CHARINDEX (' on', notes, len(notes)-25)+4, 1) in ('0','1')
I get...
2004-09-27 00:00:00.000
For the one row example I gave above...
So, why can't I do that "between" statement? Also, the formatting doesn't work on the convert but I don't really care. (unless that's the problem)
Start Free Trial
