Syntax re-arrangement

[robmason10]

Here's the dilemma....

We have a 3rd part piece of s/w that has a 'built-in' sql engine...but it isn't comprehensive...I have the following sytex which works perfectly directly on the sql db but doesn't work when integrated through this 3rd party stuff - problem is I don't know what does and does not work - I am on trail and error basis only....

So I need re-arrangements of the following syntax that would give the same result to try....

heres the line that I know is causing the troble:

AND
(tblCapacity.dtStartDate < GETDATE()) AND (tblCapacity.dtEndDate > GETDATE() OR tblCapacity.dtEndDate IS NULL)

and a virtual pint of larger for the winner!

 

[SQLSister]

Well we could try it methodically:

First have you tried each of the thrtee sections ofthat wihtout the others to see if they all run when there is only one condition? This mui=ight help you further see where the problem is. Perhaps your third party software doesn;t recognize getdate()?

If all three work ok indivudlally then there is something that the third party software doesn;t like about the (combinations. ASo try the or statement first without the and and then try the and statement with eachone fothe or parts individually and see if they work.

One possibility might be that you need more parentheses arounf the and or becasue the third party softwar is not interpreting the logic correctly. You could try:

(tblCapacity.dtStartDate < GETDATE()) AND (tblCapacity.dtEndDate > GETDATE()) OR tblCapacity.dtEndDate IS NULL)

or
(tblCapacity.dtStartDate < GETDATE()) AND ((tblCapacity.dtEndDate > GETDATE() OR tblCapacity.dtEndDate IS NULL))

Depending on which one means what you wanted.

 

[DawleyWeb]

I agree with SQLSister ... GETDATE() is a SQL Server intrinsic function and is most likely the cause of your error. If you are using a &quot;known&quot; application or language to send the sql statement to your 3rd party s/w then perhaps you could build the sql statement dynamically and use the date function from your calling app. and hard code it into the sql statement ... otherwise &quot;GOOGLE&quot; the 3rd party software to see if you can get a list of its intrinsic functions ...

Good Luck