How to Compare Two Dates to Find Time Difference in SQL Server 2005, Date Manipulation

How to compare two dates to find time difference in SQL Server 2005, date manipulation

Take a look at the DateDiff() function.

-- Syntax
-- DATEDIFF ( datepart , startdate , enddate )

-- Example usage
SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff
SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff
SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff
SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff
SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDiff
...

You can see it in action / play with it here

SQL query in SQL SERVER 2005 - Comparing Dates

declare tomorrow's date : DATEADD(dd,1,getdate())

compare dates :

WHERE column >= CONVERT(datetime, CONVERT(varchar, DATEADD(day, 1, GETDATE()), 102))
    AND column < CONVERT(datetime, CONVERT(varchar, DATEADD(day, 2, GETDATE()), 102))

Best way to compare dates without time in SQL Server

Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOOR of the value: this is then just math, not strings - much faster

declare @when datetime = GETUTCDATE()
select @when -- date + time
declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime)
select @day -- date only

In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed):

declare @when datetime = 'Feb 15 2012  7:00:00:000PM'
declare @min datetime = FLOOR(CAST(@when as float))
declare @max datetime = DATEADD(day, 1, @min)

select * from sampleTable where DateCreated >= @min and DateCreated < @max

Compare dates in T-SQL, ignoring the time part

The most reasonable way to do this is to strip away the time portion of the datetime values and compare the results, and the best way to strip the time portion from a datetime is like this:

cast(current_timestamp as date)    

I used to use and advocate a process that looked like one of the following two lines:

cast(floor(cast(getdate() as float)) as datetime)
dateadd(dd,0, datediff(dd,0, getDate()))

But now that Sql Server has the Date type, which does not hold a time component, there is little reason to use either of those techniques.

One more thing to keep in mind is this will still bog down a query if you need to do it for two datetime values for every row in a where clause or join condition. If possible you want to factor this out somehow so it's pre-computed as much as possible, for example using a view or computed column.

Finally, note the DATEDIFF function compares the number of boundaries crossed. This means the datediff in days between '2009-09-14 11:59:59' and '2009-09-15 00:00:01' is 1, even though only 2 seconds has elapsed, but the DATEDIFF in days between '2009-09-15 00:00:01' and '2009-09-15 11:59:59' is still zero, even though 86,398 seconds elapsed. It doesn't really care at all about the time portion there, only the boundaries. Depending on what your query is trying to do, you might be able to use that to your advantage.

Difference between two date& time in datetime Fields in SQL

If you're talking about SQL Server, DATEDIFF is what you need.

Something like:

SELECT DATEDIFF(d, StartDate, EndDate) FROM tableName

... just substitute StartDate and EndDate for your column names, and tableName for the actual table name.
Also, you can swap out the 'd' character for other dateparts, say if you wanted to find the date difference in months, years etc.

* Update *

@sateesh, try and follow this. I think the issue you may be having is to do with rounding in SQL's DATEDIFF function. The way around this is to go down to a more granular level, such as minutes instead of days. See the sample code below, and compare the outputs:

DECLARE @tblDummy TABLE(Value1 SMALLDATETIME, Value2 SMALLDATETIME, [Description] NVARCHAR(50))
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-02 01:00', '13 hours 0 mins')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-02 11:59', '23 hours 59 mins')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-02 13:00', '25 hours 0 mins')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-03 12:00', '48 hours 0 mins')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-03 12:01', '48 hours 1 min')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-04 00:00', '60 hours 0 mins')

-- Attempt 1: Standard date diff
SELECT DATEDIFF(d, Value1, Value2) [diff], [Description]
FROM @tblDummy

-- Attempt 2: Date diff taking it down to the minutes level
SELECT CEILING((DATEDIFF(minute, Value1, Value2) / 60.0) / 24.0) [diff], [Description]
FROM @tblDummy

Here's the output:

I believe Attempt 2 gives you what you need. If that doesn't help you, then I'm afraid I just don't understand your question.

How can I compare time in SQL Server?

Your compare will work, but it will be slow because the dates are converted to a string for each row. To efficiently compare two time parts, try:

declare @first datetime
set @first = '2009-04-30 19:47:16.123'
declare @second datetime
set @second = '2009-04-10 19:47:16.123'

select (cast(@first as float) - floor(cast(@first as float))) -
       (cast(@second as float) - floor(cast(@second as float)))
       as Difference

Long explanation: a date in SQL server is stored as a floating point number. The digits before the decimal point represent the date. The digits after the decimal point represent the time.

So here's an example date:

declare @mydate datetime
set @mydate = '2009-04-30 19:47:16.123'

Let's convert it to a float:

declare @myfloat float
set @myfloat = cast(@mydate as float)
select @myfloat
-- Shows 39931,8244921682

Now take the part after the comma character, i.e. the time:

set @myfloat = @myfloat - floor(@myfloat) 
select @myfloat
-- Shows 0,824492168212601

Convert it back to a datetime:

declare @mytime datetime
set @mytime = convert(datetime,@myfloat)
select @mytime
-- Shows 1900-01-01 19:47:16.123

The 1900-01-01 is just the "zero" date; you can display the time part with convert, specifying for example format 108, which is just the time:

select convert(varchar(32),@mytime,108)
-- Shows 19:47:16

Conversions between datetime and float are pretty fast, because they're basically stored in the same way.

SQL Server 2005 compare dates

Seems like your date compare is correct. It may be other logic that is causing this issue. Perhaps you should paste in more of your stored procedure to find the likely problem.

---

Related Topics