CODEWITHSUNDEEP
sqlsql-server-2008keywordsmalldatetime
Alex N
Alex N

Reputation: 1121

How to select data from 30 days?

I have query:

SELECT name
FROM (
SELECT name FROM 
Hist_answer
WHERE id_city='34324' AND datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
UNION ALL
SELECT name FROM 
Hist_internet
WHERE id_city='34324' AND datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
) x
GROUP BY name ORDER BY name

But DATE_SUB is a MySQL function and I need function for MsSQL 2008

Tell me please how to select data from 30 days by using MsSQL 2008?

P.S.: Data type of datetime is smalldatetime

Upvotes: 49

Views: 127514

Answers (4)

Jonatan Dragon
Jonatan Dragon

Reputation: 5017

Short version for easy use:

SELECT * 
FROM [TableName] t
WHERE t.[DateColumnName] >= DATEADD(month, -1, GETDATE())

DATEADD and GETDATE are available in SQL Server starting with 2008 version. MSDN documentation: GETDATE and DATEADD.

Upvotes: 0

maikelsabido
maikelsabido

Reputation: 1327

For those who could not get DATEADD to work, try this instead: ( NOW( ) - INTERVAL 1 MONTH )

Upvotes: 14

Rab
Rab

Reputation: 35572

You should be using DATEADD is Sql server so if try this simple select you will see the affect

Select DATEADD(Month, -1, getdate())

Result

2013-04-20 14:08:07.177

in your case try this query

SELECT name
FROM (
SELECT name FROM 
Hist_answer
WHERE id_city='34324' AND datetime >= DATEADD(month,-1,GETDATE())
UNION ALL
SELECT name FROM 
Hist_internet
WHERE id_city='34324' AND datetime >= DATEADD(month,-1,GETDATE())
) x
GROUP BY name ORDER BY name

Upvotes: 66

Anvesh
Anvesh

Reputation: 7683

Try this : Using this you can select date by last 30 days, 

SELECT DATEADD(DAY,-30,GETDATE())

Upvotes: 24

Related Questions