Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 333

# SQL Date diff function

I am trying to do a datefiff function in SQL, and I keep getting the error: 'Date' is not a recognized built-in function name.

What am I doing wrong? How can I write this in SQL?
``````Case
Sum(IIf(DateDiff("d",[OpenedDate],Date())>7,1,0)) AS [Over 7 days],
Sum(IIf(DateDiff("d",[OpenedDate],Date())>=2 And DateDiff("d",[OpenedDate],Date())<=7,1,0)) AS [2-7 days],
Sum(IIf(DateDiff("d",[OpenedDate],Date())<=1,1,0)) AS [1 day or Less]
End,
``````
0
saved4use
1 Solution

PresidentCommented:
DATE() is not a function in SQL Server.  Perhaps you want to use GetDate() instead?
0

Hello saved4use,

SELECT
Sum(CASE WHEN DateDiff(D,[OpenedDate],GETDATE() ) >7 THEN 1 ELSE 0 END )  AS [Over 7 days],
Sum(cASE WHEN DateDiff(D,[OpenedDate],GETDATE()) >=2 And DateDiff(D,[OpenedDate],GETDATE())<=7 THEN 1 ELSE 0 END )  AS [2-7 days],
Sum(cASE WHEN DateDiff(D,[OpenedDate],GETDATE())<=1 THEN 1 ELSE 0 END ) AS [1 day or Less] FROM urTable

Regards,

aneeshattingal
0

Commented:
Shouldn't have the [] brackets in either spot.  Your name "Over 7 Days" should be just one word - see below.  Just try debugging ONE of them then apply the working code to the rest.

Sum(IIf(DateDiff("d",OpenedDate,Date())>7,1,0)) AS Over7Days

Try running that query.  Another good way to compare dates is like this:

SELECT  COUNT(*) AS Butt, CONVERT(varchar, LoginDateTime, 101) AS Another