Thursday, February 20, 2014

Getting no results in pass through sql when a date is specified

Problem
I have a SQL query like this:
SELECT * FROM tableName
WHERE DocDate BETWEEN str(StartDate) AND str(EndDate)

I'm passing this in using pass through SQL, but it's not getting results... Copying and pasting this exact SQL query into SSMS shows that there are results for it. So what's the problem?

Solution
It turns out, you can't just pass a date to SQL Server using the str() function. You have to use sqlDate() instead.

So the query above becomes:
SELECT * FROM tableName
WHERE DocDate BETWEEN sqlDate(StartDate) AND sqlDate(EndDate)

If only I had to known this before wasting 4 hours of my life, wondering why a simple query was not working.

Reference
http://support.microsoft.com/kb/929786

No comments:

Post a Comment

There was an error in this gadget