DATEDIFF insanity

[influent]

The following works only when I change the "AND" to "OR".

declare @ows_table varchar(50)
declare @more varchar(10)
declare @less varchar(10)
set @ows_table = 'ows_project2_u_trainingauthorization'
set @more = '12/20/2002'
set @less = '12/15/2002'
--this should find any results between 12/15 and 12/20
EXEC(
'SELECT tp_Body, tp_Author, tp_Title, tp_Created
FROM ' + @ows_table +
' WHERE DateDiff(day,' + @more + ',tp_Created) < 0 AND
DateDiff(day,' + @less + ',tp_Created) > 0'
)

It's actually part of a stored procedure that I converted for testing QA, that's why it looks odd. The second part of the predicate always works (@less) but the first part (@more) refuses. Any clues?

 

[Zathras]

Not sure what the problem is, but why not use BETWEEN?

' WHERE tp_Created BETWEEN ' + @less + ' AND ' @more

 

[Zathras]

You may need to quote the dates:

' WHERE tp_Created BETWEEN ''' + @less + ''' AND ''' @more + ''''

 

[tlbroadbent]

You will need quotes around the dates.

WHERE DateDiff(day,''' + @more + ''',tp_Created) < 0 AND
DateDiff(day,''' + @less + ''',tp_Created) > 0'

Why are you using the datediff function? A straight forward date comparison is simpler and query performance will be better because SQL will not have to perform a table scan and execute the function twice for each data row. Performance will seriously degrade when using the function, especially if there is an index on the tp_Created column. SQL Server will use the index when executing the query below but will not use the index when executing the query above.

' WHERE tp_Created Between ''' + @less ''' And '''' + @more + '''' Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[influent]

Thanks. I'm somewhat new to SQL, so I didn't know about the BETWEEN function. I'll try that.

I thought I tried it with the three quotes and it didn't work because it's in an EXEC statement (which already has quotes), but maybe not.

 

[tlbroadbent]

There is a syntax error in the SQL I suggested. Here is the corrected code.

' WHERE tp_Created Between ''' + @less + ''' And ''' + @more + '''' Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[influent]

Yup, I had noticed that. I was a little confused at first, but I figured it was just a typo.