x

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')
more ▼

asked Nov 19, 2009 at 11:59 AM in Default

Greg J gravatar image

Greg J
1 1 1 1

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.
Nov 19, 2009 at 12:28 PM TG
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.
Nov 19, 2009 at 01:06 PM Greg J
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Nov 19, 2009 at 03:25 PM

TG gravatar image

TG
1.8k 1 3

TG thanks again for your help. here are some values from field 19.

192 19 10/1/2009 09:45 AM
193 19 10/5/2009 7:20 AM
194 19 10/5/2009 8:20 AM
195 19 10/6/2009 7:00 AM
198 19 10/6/2009 8:00 AM
200 19 10/6/2009 8:10 AM
201 19 10/6/2009 4:30 PM
202 19 10/8/2009 11:45 PM

Unfortunately, when I added the convert statement to the where clause I got the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

Do I need to use a CAST function to convert it from text to datetime?
Nov 19, 2009 at 04:56 PM Greg J
(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Nov 20, 2009 at 09:23 AM

RickD gravatar image

RickD
1.7k 1 1 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x369
x94

asked: Nov 19, 2009 at 11:59 AM

Seen: 1170 times

Last Updated: Nov 19, 2009 at 02:37 PM