How do you test if something is older than 3 months?

Question

I have been having some trouble to select the rows of my table which has a date of 3 months prior of today. I tried using DATE(NOW() - INTERVAL 3 MONTH) in my where clause, but no luck. How do I check in SQL Server if a item is older than 3 months?

  UPDATE[TCTdb].[dbo].[Stock]
     SET[Warehouse] = 'old'
   WHERE [ManufacturedDate] <= DATE(NOW() - INTERVAL 3 MONTH)

is it actual months or is it 90 days? would it be 5/3 or older right? — Keng, Aug 03 '11 at 18:50

score 46 · Accepted Answer · answered Aug 03 '11 at 18:53

46

Your syntax appears to be wrong.

That should be

UPDATE[TCTdb].[dbo].[Stock]
    SET[Warehouse] = 'old'
WHERE [ManufacturedDate] <= DATEADD(mm, -3, GETDATE())

answered Aug 03 '11 at 18:53

Code Magician

23,217
7
60
77

3

++1 This format will also work best with any indexing you may have on the ManufacturedDate field – Sam DeHaan Aug 03 '11 at 18:55

canon · Answer 2 · 2015-05-15T16:42:19.873

9

Use dateadd().

update [TCTdb].[dbo].[Stock]
set [WareHouse] = 'old'
where [ManufacturedDate] < dateadd(month,-3,getdate())

I suggest dateadd() over datediff() because I think you're going to get unexpected results using datediff() with the month datepart.

Consider that the following statements both return 3:

select datediff(month, '1/1/2011','4/1/2011')
select datediff(month, '1/1/2011','4/30/2011')

Either works in this particular case... Just keep that behavior in mind.

edited May 15 '15 at 16:42

answered Aug 03 '11 at 18:58

canon

40,609
10
73
97

1

+1 for linking to the DATEADD article on MSDN so I could find out what other values besides "month" are valid for the first argument. – Jon Schneider Mar 16 '15 at 20:24

score 2 · Answer 3 · answered Aug 03 '11 at 18:51

2

The DATEDIFF function should be helpful to you:

http://msdn.microsoft.com/en-us/library/ms189794.aspx

UPDATE[TCTdb].[dbo].[Stock]
    SET[Warehouse] = 'old'
    WHERE DATEDIFF(month, [ManufacturedDate], GETDATE()) > 3

answered Aug 03 '11 at 18:51

hspain

17,528
5
19
31

-1 DATEDIFF is wrong. 01 Jan to 30 Apr is 3 months difference for example @marc_s: it's actually wrong – gbn Aug 03 '11 at 19:35
He is looking for 3 months, not 90 days. 01 Jan to 30 Apr is 3 months. How many months would you say it is? – hspain Aug 03 '11 at 20:18
1

@hspain: usign DATEADD with -3 months isn't 90 days. I subtracts 3 calendar months. If I wanted stuff older then 3 months on 30 April, I'd expect a cut off of 30 January. A business user would see it that way too: – gbn Aug 04 '11 at 19:09

score 1 · Answer 4 · answered Aug 03 '11 at 18:51

1

try out DATEDIFF:

SELECT 
    case 
        when DATEDIFF(month, '2005-12-31' , '2006-04-01 ') > 3
        then 'yes'

        else 'no'

    end

Hope that helps,

John

answered Aug 03 '11 at 18:51

JohnD

14,327
4
40
53

DATEDIFF is wrong. 01 Jan to 30 Apr is 3 months difference for example -1 – gbn Aug 03 '11 at 19:34

How do you test if something is older than 3 months?

4 Answers4

Linked