Date Restriction No Longer Working. Please help!

Question

question

Greg J asked Nov 19, '09

Date Restriction No Longer Working. Please help!

Hi! I wrote the following script that restricted just fine for dates prior to 10/1/9. So for example, s19.DataContent >= '9/1/9' and s19.DataContent <= DATEADD(day,1,'9/30/9') works perfectly but s19.DataContent >= '10/1/9' and s19.DataContent <= DATEADD(day,1,'10/30/9') does not. I'm assuming it has something to do with how the date is stored but I can't figure out. Any help is greatly appreciated!

                    
select s1.TicketID,                    
s3.DataContent as [Requester],                    
s5.DataContent as [Department],                    
s7.DataContent as [Program],                    
s10.DataContent as [Category],                    
s11.DataContent as [Resolution],                    
s12.DataContent as [Cause],                    
s14.DataContent as [Description of Problem],                    
s16.DataContent as [Description of Resolution],                    
s17.DataContent as [Total Ticket Time],                    
s18.DataContent as [Assigned To?],                    
s19.DataContent as [TS Start Time],                    
s20.DataContent as [TS End Time]                    
                    
FROM st_CustomDataFieldsinTickets s1                    
                    
 JOIN st_CustomDataFieldsinTickets s3 on s1.TicketID = s3.TicketID and s3.customFieldID = 3                    
 JOIN st_CustomDataFieldsinTickets s5 on s1.TicketID = s5.TicketID and s5.customFieldID = 5                    
 JOIN st_CustomDataFieldsinTickets s7 on s1.TicketID = s7.TicketID and s7.customFieldID = 7                    
 JOIN st_CustomDataFieldsinTickets s10 on s1.TicketID = s10.TicketID and s10.customFieldID=10                    
 JOIN st_CustomDataFieldsinTickets s11 on s1.TicketID = s11.TicketID and s11.customFieldID=11                    
 JOIN st_CustomDataFieldsinTickets s12 on s1.TicketID = s12.TicketID and s12.customFieldID=12                    
 JOIN st_CustomDataFieldsinTickets s14 on s1.TicketID = s14.TicketID and s14.customFieldID=14                    
 JOIN st_CustomDataFieldsinTickets s16 on s1.TicketID = s16.TicketID and s16.customFieldID=16                    
 JOIN st_CustomDataFieldsinTickets s17 on s1.TicketID = s17.TicketID and s17.customFieldID=17                    
 JOIN st_CustomDataFieldsinTickets s18 on s1.TicketID = s18.TicketID and s18.customFieldID=18                    
 JOIN st_CustomDataFieldsinTickets s19 on s1.TicketID = s19.TicketID and s19.customFieldID=19                    
 JOIN st_CustomDataFieldsinTickets s20 on s1.TicketID = s20.TicketID and s20.customFieldID=20                    
                    
WHERE s1.customFieldID = 1 and s19.DataContent >= '10/1/9' and s19.DataContent <= DATEADD(day,1,'10/30/9')

sql-server-2005 query datetime

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TG commented · Nov 19, 2009 at 12:28 PM

What specifically doesn't work? What is the sql datatype of [DataContent] ? Post one of the values that should be returned based on the criteria.

0 ·

Greg J commented · Nov 19, 2009 at 01:06 PM

Hey TG. The date restriction is not working for 10/1/9 to 10/31/9 or any date resriction after 9/30/9. Looks like the DataContent sql datatype is (nvarchar(512), not null). I cannot change any data types since this is a third party vendor db.

0 ·

Answer 1 · 2009-11-19T15:25:29Z

TG answered Nov 19, '09

Ok - the vendor provided a completely generic table with all content datatypes as nvarchar. that is unfortunate. Please post some dataContent values for customerFieldID = 19 so we can see if the value can be directly converted to datetime. If they can be then you can say,

convert(datetime, s19.dataContent) >= '2009-10-01'

otherwise you'll have to manipulate the string to something that can be converted to datetime. Another thing I'd suggest is replace all those JOINs to different instances of the same table with something like this:

select  s1.TicketID            
       ,max(case d.customFieldID = 3 then d.dataContent) end as Requester            
       ,max(case d.customFieldID = 5 then d.dataContent) end as Department            
       ,max(case d.customFieldID = 7 then d.dataContent) end as Program            
       --etc            
from   (--get all tickets that satisfy the date criteria            
       select ticketID            
       from   st_CustomDataFieldsinTickets            
       where  customFieldID = 19            
       and    convert(datetime, dataContent) >= '2009-10-01'            
       and    convert(datetime, dataContent) <= DATEADD(day,1,'2009-10-30')            
       ) s1            
--get all other values in one table            
join   st_CustomDataFieldsinTickets d            
       on d.ticketid = s1.ticketID            
where  d.customFieldID in (3,5,7) --,10,11,12,14,16,17,18,19,20)            
group by st.TicketID

EDIT: Assuming your comment is accurate and that first two parts ("199 19.") are not part of the dataContent value it looks like one of your values is not well formed. It is missing a "/" separator. You would need to exclude those value or clean them up. That is one of the problems of storing data in untyped columns.

select dataContent            
       ,convert(datetime, dataContent) as dataContentConverted            
from   (            
       select convert(nvarchar(255), '10/1/2009 09:45 AM') as dataContent            
       union all select '10/5/2009 7:20 AM'             
       union all select '10/5/2009 8:20 AM'            
       --union all select '19 10/6/2009 7:00 AM'            
       union all select '10/6/2009 8:00 AM'            
       union all select '10/6/2009 8:10 AM'            
       union all select '10/6/2009 4:30 PM'            
       union all select '10/8/2009 11:45 PM'            
       ) d

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-11-20T09:23:40Z

RickD answered Nov 20, '09

So, if one row of the dataContent looks like this : 192 19 10/1/2009 09:45 AM

You could do:

convert(datetime,substring(dataContent,8,len(dataContent))) >= '2009-10-01'

If this doesn't work, can you post any that are different to this format?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question