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