Difference between two date& time in datetime Fields in SQL [duplicate]

Question

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

I had two datetime fields and should calculate the between them including time fields.

If the difference between them is less than 24 hours it should consider as 1 day and 24 to 48 hours as 2 days.

Answer 1

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.