How to calculate difference in hours decimal between two dates in SQL Server

Question

I have to calculate the difference in hours  decimal type  between two dates in SQL Server 2008   I couldn t find any useful technique to convert datetime to decimal with  CONVERT  on MSDN  Can anybody help me with that   UPDATE  To be clear  I need the fractional part as well  thus decimal type   So from 9 00 to 10 30 it should return me 1 5

User · Answer

Declare  date1 datetime Declare  date2 datetime  Set  date1    11 20 2009 11 00 00 AM  Set  date2    11 20 2009 12 00 00 PM   Select Cast DateDiff hh   date1   date2  as decimal 3 2   as HoursApart   Result   1 00

User · Answer

DATEDIFF but note it returns an integer so if you need fractions of hours use something like this -  CAST DATEDIFF ss  startDate  endDate  AS decimal precision  scale     3600

User · Answer

Using Postgres I had issues with DATEDIFF  but had success with this     DATE PART  day   delivery time   timestamp -  placed time   timestamp    24      DATE PART  hour   delivery time   timestamp -  placed time   timestamp      DATE PART  minute   delivery time   timestamp -  placed time   timestamp    60   which  gave me an output like  14 3

User · Answer

Just subtract the two datetime values and multiply by 24    Select Cast   DateTime2 -  DateTime1  as Float    24 0  a test script might be    Declare  Dt1 dateTime Set  Dt1    12 Jan 2009 11 34 12    Declare  Dt2 dateTime Set  Dt2   getdate      Select Cast   Dt2 -  Dt1  as Float    24 0  This works because all datetimes are stored internally as a pair of integers  the first integer is the number of days since 1 Jan 1900  and the second integer  representing the time  is the number of  1  ticks since Midnight    For SmallDatetimes the time portion integer is the number of minutes since midnight   Any arithmetic done on the values uses the time portion as a fraction of a day     6am   0 25  noon   0 5  etc      See MSDN link here for more details  So Cast   Dt2 -  Dt1  as Float  gives you total days between two datetimes   Multiply by 24 to convert to hours   If you need total minutes  Multiple by Minutes per day  24   60   1440  instead of 24    NOTE 1  This is not the same as a dotNet or javaScript tick - this tick is about 3 33 milliseconds

User · Answer

SELECT DATEDIFF hh  firstDate  secondDate  FROM tableName WHERE

User · Answer

DATEDIFF minute startdate enddate  60 0    Or use this for 2 decimal places   CAST DATEDIFF minute startdate enddate  60 0 as decimal 18 2

User · Answer

You are probably looking for the DATEDIFF function      DATEDIFF   datepart   startdate   enddate      Where you code might look like this   DATEDIFF   hh   startdate   enddate

User · Answer

DATEDIFF hour  start date  end date  will give you the number of hour boundaries crossed between start date and end date   If you need the number of fractional hours  you can use DATEDIFF at a higher resolution and divide the result   DATEDIFF second  start date  end date    3600 0   The documentation for DATEDIFF is available on MSDN   http   msdn microsoft com en-us library ms189794 28SQL 105 29 aspx

[sql-server] How to calculate difference in hours (decimal) between two dates in SQL Server?

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to decimal