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
  • Last Modified:

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,

Open in new window

0
saved4use
Asked:
saved4use
1 Solution
 
Bill BachPresidentCommented:
DATE() is not a function in SQL Server.  Perhaps you want to use GetDate() instead?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
MrJesse34Commented:
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
FROM     kpmLoginLog
GROUP BY CONVERT(varchar, LoginDateTime, 101)
ORDER BY Another DESC

Grouping by just the date piece in DateTime field from SQL.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now