CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
TSCAmerica.com
TSCAmerica.com

Reputation: 5377

SQL QUERY ( SQL SERVER) Date & Time Difference

I have a SQL SERVER Table which has only one field "StartDate" the records are as follows

** 2011-07-28 19:30:00.000

2011-07-29 21:50:00.000

2011-07-25 09:20:00.000 **

What i want to do is :

SHOW RECORDS if its CURRENT DATE ( todays date ) and the time difference between current time the StartDate is not less then 5 minutes, i have written the following code but it doesnt show me the time difference ?

SELECT * FROM table WHERE DATEDIFF(day, StartDate, GETDATE()) <= 0

Upvotes: 2

Views: 6089

Answers (4)

Craig Celeste
Craig Celeste

Reputation: 12653

Using BETWEEN is probably a little more optimal than two AND statements (maybe not). Try not to do a calculation on each row if you don't have to. Doing DATEADD only on the current date will only be calculated once.

SELECT 
   whatever
FROM
   table
WHERE 
    StartDate
        BETWEEN FLOOR( CAST( GETDATE() AS FLOAT ) )
        AND DATEADD(minute, -5, GETDATE())

I interpret the question as looking for rows where the date is today (between the start of today) but not within the last 5 minutes (and less than 5 minutes ago). That might not be what you were going for.

Hope that helps

Upvotes: 0

KenL
KenL

Reputation: 875

There are two ways to do it one being DateDiff the other is DATEADD. Judging by the way I read your question DateAdd should do it. Here is an example;

    SELECT *
  FROM [dbo].[TABLE]
  WHERE [LAST_UPDATE] > DATEADD(minute,-5,GetDate())

Upvotes: 0

Curtis
Curtis

Reputation: 103358

SELECT StartDate
FROM table
WHERE YEAR(StartDate)=YEAR(GETDATE())
AND MONTH(StartDate)=MONTH(GETDATE())
AND DAY(StartDate)=DAY(GETDATE())
AND (DATEDIFF(minute, StartDate, GETDATE()) >= 5
     OR
     DATEDIFF(minute, StartDate, GETDATE()) <= 5)

Upvotes: 3

Cade Roux
Cade Roux

Reputation: 89661

How about:

SELECT StartDate
    ,GETDATE()
    ,DATEDIFF(day, StartDate, GETDATE())
    ,DATEDIFF(minute, StartDate, GETDATE())
    ,*
FROM table
WHERE DATEDIFF(day, StartDate, GETDATE()) <= 0
    AND DATEDIFF(minute, StartDate, GETDATE()) >= 5

Upvotes: 0

Related Questions