CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2008-r2
Rman Edv
Rman Edv

Reputation: 163

Comparing dates in SQL Server

I have a DateTime column named EXP_Date which contains date like this :

2014-07-13 00:00:00.000

I want to compare them, like this query :

SELECT COUNT(*)
FROM DB
WHERE ('2014-07-15' - EXP_DATE)  > 1

I expect to see the number of customers who have their services expired for over a month.

I know this query wouldn't give me the correct answer, the best way was if I separate the Year / Month / Day into three columns, but isn't any other way to compare them as they are?

Upvotes: 0

Views: 126

Answers (4)

Orlando Herrera
Orlando Herrera

Reputation: 3531

Another way using DATEDIFF

SET DATEFORMAT DMY  --I like to use "dateformat"

SELECT  COUNT(*)
FROM DB
WHERE (DATEDIFF(DAY,@EXP_DATE,GETDATE())) >= 30 --Remember, instead "Day" you can use week, month, year, etc

Syntax: DATEDIFF ( datepart , startdate , enddate ) Depart: year, quarter, month, day, week...

For more information you can visit MSDN

Upvotes: 2

mohan111
mohan111

Reputation: 8865

 SELECT COUNT(EXPIRE)FROM 
 (Select CASE WHEN  EXP_DATE < DATEADD(month, -1, GETDATE())THEN 1 ELSE 0 END)AS EXPIRE FROM DB
  )tt

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

Try this

 SELECT COUNT(*)
 FROM DB
 where  DATEADD(month, -1, GETDATE()) > EXP_DATE

Upvotes: 2

juergen d
juergen d

Reputation: 204766

You can use DATEADD

 SELECT COUNT(*)
 FROM DB
 where EXP_DATE < DATEADD(month, -1, GETDATE())

Upvotes: 4

Related Questions