## How to get difference between two dates in days and hours?

 Author Message Srikanth 21 SSC Veteran Group: General Forum Members Points: 228 Visits: 275 Hi, I'm looking code to find difference between two dates in days and hours. Example:date1: 2013-04-07 14:45:41.013 - date2: 2013-04-05 10:45:41.013I need output like 2 days and 6 hours? bitbucket-25253 SSC Guru Group: General Forum Members Points: 56299 Visits: 25280 Use the DATEDIFF functions ... for example SELECT DATEDIFF ( datepart , startdate , enddate )This will return the number of days difference.SELECT DATEDIFF(d,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')returns 2 daysThis returns the total number of hours difference (52)SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')--combining every thing I think will give you what you are looking for:` SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24 AS 'Days' ,DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013') - (DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24)*24 AS 'Hours'Result:Days Hours2 4 ` If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read Srikanth 21 SSC Veteran Group: General Forum Members Points: 228 Visits: 275 Thanks, It's worked. dsharmadbec Grasshopper Group: General Forum Members Points: 19 Visits: 2 +xbitbucket-25253 - Sunday, April 7, 2013 2:05 PMUse the DATEDIFF functions ... for example SELECT DATEDIFF ( datepart , startdate , enddate )This will return the number of days difference.SELECT DATEDIFF(d,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')returns 2 daysThis returns the total number of hours difference (52)SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')--combining every thing I think will give you what you are looking for:` SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24 AS 'Days' ,DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013') - (DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24)*24 AS 'Hours'Resultays Hours2 4 `This will only works, when minutes and seconds are same of both the dates, but if we change minutes then values are not correct.Like: SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:00:41.013')I have changed the minutes in this query,After executing this query we still get the same output i.e. 4 hoursBut According to me output should be 3 hours,Can you tell me how we can get 3 hours output.Please helpThanksDeepak Sharma george_at_sql Ten Centuries Group: General Forum Members Points: 1155 Visits: 1717 Since the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..`SELECT DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/ ,DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60/60 as hrs_diff /*Convert the difference in Micro Seconds as Hours*/ ,(DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60) % 60 as min_diff /*Gets the residual minutes*/` Lynn Pettis SSC Guru Group: General Forum Members Points: 380177 Visits: 42190 +xdsharmadbec - Tuesday, March 13, 2018 8:00 AM+xbitbucket-25253 - Sunday, April 7, 2013 2:05 PMUse the DATEDIFF functions ... for example SELECT DATEDIFF ( datepart , startdate , enddate )This will return the number of days difference.SELECT DATEDIFF(d,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')returns 2 daysThis returns the total number of hours difference (52)SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')--combining every thing I think will give you what you are looking for:` SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24 AS 'Days' ,DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013') - (DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24)*24 AS 'Hours'Resultays Hours2 4 `This will only works, when minutes and seconds are same of both the dates, but if we change minutes then values are not correct.Like: SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:00:41.013')I have changed the minutes in this query,After executing this query we still get the same output i.e. 4 hoursBut According to me output should be 3 hours,Can you tell me how we can get 3 hours output.Please helpThanksDeepak SharmaYou need to understand that DATEDIFF is counting the number of boundaries you are crossing based on the date type you are using, which in your case is hours (hh). Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) sgmunson SSC Guru Group: General Forum Members Points: 85852 Visits: 6727 +xgeorge-178499 - Tuesday, March 13, 2018 8:24 AMSince the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..`SELECT DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/ ,DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60/60 as hrs_diff /*Convert the difference in Micro Seconds as Hours*/ ,(DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60) % 60 as min_diff /*Gets the residual minutes*/`Also, your DATEDIFF is choosing milliseconds, not microseconds. Steve‌(aka sgmunson)‌ ‌Health & Nutrition Jeff Moden SSC Guru Group: General Forum Members Points: 846441 Visits: 46650 First, for all those that are about to remind us that this post was necro'd from a 5 year old post, we already know that. Please drive through. What the original post is looking for is a simple "interval" or "duration". Please see the following article for a simple way to do this and modify the formatting section for however you see fit.Calculating Duration Using DATETIME Start and End Dates (SQL Spackle) --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Sergiy SSC Guru Group: General Forum Members Points: 96269 Visits: 13914 Or use a function described in this article:http://www.sqlservercentral.com/articles/Datetime+conversions/153316/ george_at_sql Ten Centuries Group: General Forum Members Points: 1155 Visits: 1717 +xsgmunson - Tuesday, March 13, 2018 12:57 PM+xgeorge-178499 - Tuesday, March 13, 2018 8:24 AMSince the date you are comparing has a precision at the microsecond level you would need to use the precision you are using to determine the difference between dates..`SELECT DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015') /*Difference in Micro seconds between two dates*/ ,DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60/60 as hrs_diff /*Convert the difference in Micro Seconds as Hours*/ ,(DATEDIFF(ms,'2013-04-07 10:45:41.013','2013-04-07 14:00:41.015')/1000/60) % 60 as min_diff /*Gets the residual minutes*/`Also, your DATEDIFF is choosing milliseconds, not microseconds.Oh yea :-). The precision is at the millisecond level. The output computed it at the milli second itself though.