• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2926
  • Last Modified:

check if data is older than 24 hours

hi guys

i have a table customer that has following columns

customer-ID  insert_time
100                2013-05-17 8:17:32.197
200                2013-05-15 18:17:32.197


how can i write a sql which will bring me data older than a day
I am trying to do
( CONVERT (date, SYSDATETIME()) - cast(insert_time AS Date) ) > 1


but get error

Operand data type date is invalid for subtract operator.


any ideas appreaited
thanks
0
royjayd
Asked:
royjayd
  • 3
2 Solutions
 
Ross TurnerManagement Information Support AnalystCommented:
Try
Where datediff(hour,insert_time,getdate()) <24
0
 
Patrick MatthewsCommented:
If you mean "only return it if insert_time was more than 24 hours ago":

SELECT customer_ID, insert_time
FROM customer
WHERE insert_time < DATEADD(hour, -24, GETDATE())

Open in new window

0
 
Patrick MatthewsCommented:
If you really mean, "older than yesterday"...

SELECT customer_ID, insert_time
FROM customer
WHERE insert_time < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)

Open in new window

0
 
Patrick MatthewsCommented:
Try
Where datediff(hour,insert_time,getdate()) <24

Be careful with DATEDIFF.  DATEDIFF does NOT measure elapsed time.  Rather, it measures time period boundaries.

Consider this expression:

DATEDIFF(hour, '2013-05-01 00:59:59', '2013-05-02')

That will return 24, even the true elapsed time is 23:00:01.

The smaller the interval you use with DATEDIFF, the smaller the possible error--for example, using seconds you would never be off by more than a second--but there is still the possibility of error.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now