February 18, 2009 at 3:08 pm
I have a date1 field in the format: 2000-11-19 20:58:38.870
and I have a comments field with value of "data one yyy uuu 11/19/2000 8:58 PM",
I am trying to find if the date1 value matches with the datetimestamp of the comments field,
if matches then I want have a column of "Matches" as Yes/No, below is the sample data of
date1 and comments fields:
date1comments
2000-11-19 20:58:38.870comment1 xxx yyyy 11/19/2000 8:58 PM
2000-11-20 08:13:05.230comment2 aaa ccccbbbb 11/20/2000 8:13 AM
2000-11-22 08:20:05.230comment4 aaa ccccbbbb 11/22/2000 8:45 AM
2000-11-21 08:15:05.230comment6 aaa ccccbbbb 11/21/2000 8:15 AM
I want to get the output as follows:
date1commentsdatePresentinComments Matches(Yes/No)
Thanks!
February 18, 2009 at 3:30 pm
[font="Verdana"]Try something like:
select date1,
comments,
case
when comments like '%[01][0-9]/[0-3][0-9]/[12][90][0-9][0-9] [0-9]:[0-5][0-9] [AP]M%' or
comments like '%[01][0-9]/[0-3][0-9]/[12][90][0-9][0-9] 1[0-2]:[0-5][0-9] [AP]M%'
then 'Yes'
else 'No'
end as datePresentInComments,
case
when comments like
'%' + convert(varchar, date1, 101) + ' ' +
case
when ((datepart(hour, date1) % 12) < 10)
then stuff(substring(convert(varchar, date1, 109), 14, 13), 5, 7, ' ')
else stuff(substring(convert(varchar, date1, 109), 13, 14), 6, 7, ' ')
end +
'%'
then 'Yes'
else 'No'
end as [Matches(Yes/No)]
from (
select cast('20001119 20:58:38:870' as datetime) as date1,
'comment1 xxx yyyy 11/19/2000 8:58 PM' as comments
union all
select cast('20001120 08:13:05.230' as datetime) as date1,
'comment2 aaa ccccbbbb 11/20/2000 8:13 AM' as comments
union all
select cast('20001122 08:20:05.230' as datetime) as date1,
'comment4 aaa ccccbbbb 11/22/2000 8:45 AM' as comments
union all
select cast('20001121 08:15:05.230' as datetime) as date1,
'comment6 aaa ccccbbbb 11/21/2000 8:15 AM' as comments
) x
Hmmm. Make a little tricky by the fact that there's no convert() format that matches the dates in the strings, and that the hour format is variable length (depending on whether the hour is one digit or two digits). Let's see if anyone else can come up with a more succinct approach.
[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply