Comparing dates in SQL Server

Question

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?

What datatype are you using in your table? And which version of SQL Server (2000, 2005, 2008, 2008 R2, 2012, 2014) ?? — marc_s
– marc_s, Commented Jul 15, 2014 at 13:49

juergen d · Accepted Answer · 2014-07-15 13:19:06Z

4

You can use DATEADD

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

answered Jul 15, 2014 at 13:19

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

juergen d Over a year ago

For some people it might. I like this one better.

Raphaël Althaus Over a year ago

Yup, that's just a point of view, indeed... Was just wondering if there was a specific reason ;)

Rman Edv Over a year ago

what if i want to see the number of customers who have been at least 2 months expired and not more than 5 months, 2 < (exp_date - today) <5

juergen d Over a year ago

@RmanEdv: where EXP_DATE < DATEADD(month, -2, GETDATE()) AND EXP_DATE >= DATEADD(month, -5, GETDATE())

Rahul Tripathi · Accepted Answer · 2014-07-15 13:20:01Z

2

Try this

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

answered Jul 15, 2014 at 13:20

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Comments

mohan111 · Accepted Answer · 2014-07-15 13:28:41Z

2

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

answered Jul 15, 2014 at 13:28

mohan111

8,8854 gold badges35 silver badges61 bronze badges

1 Comment

Rman Edv Over a year ago

did you test it ? it doesn't work, this error : Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'FROM'. actually it is very useful to have that case

Orlando Herrera · Accepted Answer · 2014-07-15 14:42:33Z

2

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

answered Jul 15, 2014 at 14:42

Orlando Herrera

3,5491 gold badge36 silver badges45 bronze badges

Collectives™ on Stack Overflow

Comparing dates in SQL Server

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related