Easy SQL Date test against a Timestamp

[wooddsw]

Working with Dates/Timestamps have always been a challenge for me within SQL and based on the threads many others as well.

I've been troubleshooting a problem query that's not meshing with a Crystal report. Just for kicks I displayed the query within Crystal and was surprised at the way Crystal developed the date range test. I've included a code snipet and wondered if anyone is familiar with the syntax of the where clause. I've never seen this before and it appears very easy to manage. If anyone can point me to some documentation that would be great. While I'm willing to take things on faith I'd like to understand how this functionality works.

select * from salesorder SO
inner join salesorderitems SOI on SO.salesorderid = SOI.salesorderid
where So."ORDERDATE" >= {d '2006-08-04'} AND
SO."ORDERDATE" <= {ts '2006-08-04 23:59:59.000'}
order by SOI.Product desc

Go forth and date test!

 

[r937]

those are ODBC formats for date and timestamp

http://msdn2.microsoft.com/en-us/library/ms190234.aspx

r937.com | rudy.ca

 

[gmmastros]

I would suggest a slight modification to the query. It will probably never affect you, but it's probably best to be thorough now so that you never have to worry about it.

Here's the problem. Suppose you are tracking sales orders from a website, so that your sales can occur at any time, day or night. Also suppose that the OrderDate column is generated by the system (using the server's date and time). Since the GetDate() function has precision in the fractional seconds, it is technically possible to have a sales order date of '2006-08-04 23:59:59[!].500[/!]'. This record would not be returned by this query and would 'slip through the cracks'. It's not likely to occur, but it's also easy to accomodate, so why not.

select * 
from   salesorder SO 
       inner join salesorderitems SOI 
         on SO.salesorderid = SOI.salesorderid
where  So."ORDERDATE" >= {d '2006-08-04'} 
       AND SO."ORDERDATE" [!]< {d '2006-08-05'}[/!]
order by SOI.Product desc

Notice that the <= was changed to <



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[r937]

george, i was going to suggest the same thing (open-ended upper limit on the range) but maybe crystal won't let you do that??



r937.com | rudy.ca

 

[gmmastros]

Rudy, I haven't used Crystal in about 5 years. I'd be surprised if it didn't allow it though.

Maybe wooddsw will post back and let us know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[wooddsw]

This app is using an OLE DB provider for SalesLogix and I never considered it being an ODBC/OLE DB syntax. Thanks for the update.

While this app is taking the date from a text source and not using the system getdate() function I don't think the fractals precision is critical. However, I see where records can fall throught the cracks and it is something to keep on the radar for other usage.

Thanks all for the feedback