How do you test if something is older than 3 months?
I have been having some trouble to select the rows of my t开发者_如何学JAVAable 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)
Your syntax appears to be wrong.
That should be
UPDATE[TCTdb].[dbo].[Stock]
SET[Warehouse] = 'old'
WHERE [ManufacturedDate] <= DATEADD(mm, -3, GETDATE())
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.
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
try out DATEDIFF:
SELECT
case
when DATEDIFF(month, '2005-12-31' , '2006-04-01 ') > 3
then 'yes'
else 'no'
end
Hope that helps,
John
精彩评论