开发者

Select records from SQL Server if greater than 6 months

I am creating an MVC app where I need to send out an email to all those records whose valu开发者_运维百科e (DateUpdated) are not updated in the Customer table.

My Customer table looks like this:

ID (PK)  
Name  
Address  
DateLastUpdated

Now I have this MySql query:

 SELECT * FROM users WHERE DateLastUpdated >= NOW() - INTERVAL 6 MONTH 

How I will write it in T-SQL and perform this task in MVC3? Please help!


Your query should be:

SELECT * FROM users WHERE DateLastUpdated >= DateAdd(month, -6, getdate())

Additionally, you may want to strip out the time portion so you are left with just the date e.g. 2011-04-12 00:00:00.000 you can use this:

SELECT  *  
FROM    users  
WHERE   DateLastUpdated >= DateAdd(Month, -6, Cast(Floor(Cast(GetDate() as Float)) as DateTime))


The date add function can be used to add and subtract dates

SELECT  *  FROM    users  WHERE   DateLastUpdated >= DateAdd(month, -6, getDate())

You can find more info here:


This is more like a supplement. If your DateLastUpdated has hours/minutes/seconds/milliseconds components, you might find some records you expect being left out. You can always do something like

SELECT * FROM users WHERE CONVERT(DATETIME, CONVERT(NVARCHAR(10), DateLastUpdated, 102), 102) >= DateAdd(month, -6, getdate())


SELECT * FROM users WHERE DateLastUpdated >= DATE_SUB(now(), INTERVAL 6 MONTH)


Try:

SELECT * FROM users
WHERE DateLastUpdated >= DATEADD(Month, -6, GETDATE())


SELECT  *  FROM    users  
WHERE   DateLastUpdated >= dateadd(month, -6, datelastupdated)

Use the dateadd function.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜