CODEWITHSUNDEEP
sqlsql-serverdate
jason
jason

Reputation: 7164

Date time interval in SQL

I have a Column called CrateDate, I want to retrieve records whose CreateDate is last two days. How can I do that? My failed attempt is this : 

SELECT COUNT (*)
  FROM [MyDB].[dbo].[MyTable] WHERE City = 'Paris' AND CreateDate >= '2016-12-07' AND CreateDate <= '2016-12-10'

Upvotes: 2

Views: 859

Answers (8)

Vijay
Vijay

Reputation: 13

select count(*)
from MyTable
where CreateDate between dateadd(dd,-2, getdate()) and getdate() and City = 'Paris'

Upvotes: 0

zhou yun
zhou yun

Reputation: 91

If the all records saved in the same time zone with server, so below is OK：

SELECT COUNT (*)
FROM [MyDB].[dbo].[MyTable] 
WHERE City = 'Paris' AND CreateDate >= DATEADD(day, -2, GETDATE())

If the records saved in different time zones, maybe need know the exaclty timezone every city. If did not save the corresponding time zone, use the UTC maybe a better way:

SELECT COUNT (*)
FROM [MyDB].[dbo].[MyTable]
WHERE City = 'Paris' AND CreateDate >= DATEADD(day, -2, GETUTCDATE())

Upvotes: 0

Stephan Bauer
Stephan Bauer

Reputation: 9249

You can make use of DATEDIFF() and GETDATE()

SELECT COUNT (*)
FROM [MyDB].[dbo].[MyTable] 
WHERE City = 'Paris' 
  AND DATEDIFF(DAY,CreateDate,GETDATE()) between 0 and 2

This will only check the date-part of CreateDate and ignore the time. (That means if you run the query on '2016-12-09 10:11', it will also get data with CreateDate='2016-12-07 08:17'

Upvotes: 2

connectedsoftware
connectedsoftware

Reputation: 7087

Use DATEADD to subtract 2 days from the current date and check that Created Date is between that and today's date: 

SELECT COUNT (*)
  FROM [MyDB].[dbo].[MyTable]
  WHERE City = 'Paris' AND CreateDate
      BETWEEN DATEADD(DAY, -2, GETUTCDATE()) AND GETUTCDATE()

If you want whole days (i.e. no time component then use this):

SELECT COUNT (*)
  FROM [MyDB].[dbo].[MyTable]
  WHERE City = 'Paris' AND CreateDate
      BETWEEN  dateadd(dd,0, datediff(dd,0,DATEADD(day, -2, GETUTCDATE())))
  AND DATEADD(s, -1, dateadd(dd,0, dateadd(dd,0, datediff(dd,0,DATEADD(day, 1, GETUTCDATE()))))

If the current date is 09-Dec-2016 then the where clause of the second query would generate:

CreateDate BETWEEN 07-Dec-2016 00:00:00 and 09-Dec-2016 23:59:59

Note: Use GETDATE() if your date times are stored in local time or GETUTCDATE() if stored in UTC.

Upvotes: 2

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Try This.

  select count(*) FROM [MyDB].[dbo].[MyTable] 
  WHERE City = 'Paris' 
  and CreateDate >= DATEADD(DAY, -2, GETDATE())

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

You can use a DATEADD() function to substract the number of days from the current date to get that many days old data:

SELECT COUNT(*)
FROM MyTable
WHERE City = 'Paris'
    AND CreateDate BETWEEN DATEADD(DAY, -2, GETDATE()) AND GETDATE()

Or even the simpler (works at least for 2008R2 and up), since you're subtracting days:

SELECT COUNT(*)
FROM MyTable
WHERE City = 'Paris'
    AND CreateDate BETWEEN GETDATE()-2 AND GETDATE()

If you want to have a finer-grained interval, like 40 hours, then you have to use DATEADD(), otherwise you can stick to the implicit subtraction of the number of days you want to go back from my second query.

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

dateadd() and getdate()

select count(*)
from MyTable
where City = 'Paris'
and CreateDate between dateadd(dd,-2, getdate()) and getdate()

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Try this

SELECT COUNT (*) FROM [MyDB].[dbo].[MyTable] 
WHERE City = 'Paris' AND CreateDate >= dateadd(day,datediff(day,0,getdate()),-2) 
      AND CreateDate < dateadd(day,datediff(day,0,getdate()),1)

Upvotes: 1

Related Questions