7

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.

Community
  • 1
  • 1
sateesh
  • 87
  • 1
  • 1
  • 8

2 Answers2

13

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:

image displaying output from query

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.

Steve Kennaird
  • 1,604
  • 13
  • 21
  • hi steve i want the difference between date hours and minutes in the date fields and convert that difference as 1 day or 2 days depending on the output of the difference – sateesh Oct 17 '12 at 10:44
  • Sorry I'm having difficulty understanding exactly what you're after. Can you update your question to include some data from the database table, so we can see if you're doing anything unusual first. – Steve Kennaird Oct 17 '12 at 11:00
  • Hi Steve what i need is if i had the difference as 24:39 then it should display as 1 day and 25:10 as 2 days 49:10 as 3 days like wise.hope i am clear now – sateesh Oct 17 '12 at 13:27
  • Are the values stored in the database table as a datetime type? – Steve Kennaird Oct 17 '12 at 13:37
  • they are stored in database as smalldatetime – sateesh Oct 17 '12 at 13:42
  • Hi Steve I hope this will work for me. i will it out tomorrow and will let you know whether succeeded or not. Thank You – sateesh Oct 17 '12 at 14:52
  • how to calculate Day,hour and minute in single column – Vikram Dec 17 '15 at 05:18
2

Sounds like the DATEDIFF function would help. You can choose your DatePart and so, can get quite granular with the output.

http://msdn.microsoft.com/en-us/library/ms189794.aspx

From: http://www.w3schools.com/sql/func_datediff.asp

SELECT DATEDIFF(day,'2008-06-05 20:10:00','2008-08-05 20:10:00') AS DiffDate

Result:

DiffDate
61
NiceYellowEgg
  • 552
  • 1
  • 5
  • 13
  • hi NIce YellowEgg i want the difference between date hours and minutes in the date fields and convert that difference as 1 day or 2 days depending on the output of the difference – sateesh Oct 17 '12 at 10:45
  • You can use a time in the date field as well, i'll amend – NiceYellowEgg Oct 17 '12 at 10:52