0

I know I'm gonna 'kick myself' when I see the answer but right now I can't see it.

SELECT [faxdate]
,DATEDIFF(day, 
 CAST(SUBSTRING(RPT.FaxDate, 1, 4) + '-' + SUBSTRING(RPT.FaxDate, 5, 2) + '-' + SUBSTRING(RPT.FaxDate, 7, 2) AS DATE), getdate()) AS vDiff

faxdate     vDiff
20130704 62
20130705 61
20130705 61
20130708 58

Works great, but I want to be able to test the number of days Eg. records over 60 days. When I add this :

WHERE (DATEDIFF(day, 
 CAST(SUBSTRING(RPT.FaxDate, 1, 4) + '-' + SUBSTRING(RPT.FaxDate, 5, 2) + '-' + SUBSTRING(RPT.FaxDate, 7, 2) AS DATE), getdate()) >60)

I get: Conversion failed when converting date and/or time from character string.

I have also tried this sql as a derived table and then tested vDiff ....i'm obviously not doing it right

any help would be appreciated

ttfn

Martyn
(Cornwall England and it's not raining .. result !)

Shadwell
  • 34,314
  • 14
  • 94
  • 99
Lupii
  • 1
  • 1

1 Answers1

0

Try 

SELECT [faxdate],DAY(DATEDIFF(CAST(SUBSTRING(RPT.FaxDate, 1, 4) + '-' +
SUBSTRING(RPT.FaxDate, 5, 2) + '-' + SUBSTRING(RPT.FaxDate, 7, 2) AS DATE), 
getdate())) AS vDiff

instead of using where, use having like below

HAVING vDiff > 60
rakeshjain
  • 1,791
  • 11
  • 11
  • datediff requires 3 arguments so this didn't work (did I mention it's sql server 2008 r2 if it makes any difference) – Lupii Sep 04 '13 at 10:00
  • @Lupii DATEDIFF requires two arguments only. You chan check http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff and http://www.w3schools.com/sql/func_datediff_mysql.asp – rakeshjain Sep 04 '13 at 11:52
  • @Lupii Did you try CONVERT(SOMESTRING AS UNSIGNED INTEGER) TO make vdiff an integer for the comparision? – rakeshjain Sep 04 '13 at 12:09