How to convert Time to Decimal in SQL with an integer


 How to convert Time to Decimal in SQL with an integer

I am having a couple of issues converting time to decimal...

Essentially I have used a DATEDIFF to identify the hours and minutes between two dates and times. This produces a result of 5.45, however I would like to get the result as 5.75.

select RTRIM(DATEDIFF(second, startdate, enddate)/3600)+'.'
+ RIGHT('0'+RTRIM((DATEDIFF(second, startdate, enddate) % 3600)/60),2)
from time

I have tried a few things but I believe the issue is that this is not an integer and that's why i cant convert the minutes.

Asked By:Withdalot
||
Source

Answer #1:

Get the time difference in minutes, and divide by 60, this will place hours before the decimal separator and minutes after:

datediff(minute, startdate, enddate) / 60.0
Answered By:Guffa

Answer #2:

Select DATEDIFF(second,  startdate, enddate)/3600.0
Answered By:bummi

Answer #3:

As mentioned in this answer, "Take the DateDiff in seconds instead, and then divide by 86400.0. The decimal point is required."

Answered By:Alex Gordon
Nguồn: https://www.py4u.net/discuss/836488

Comments

Post a Comment

Popular posts from this blog

ASP.NET MVC + AdminLTE

Image
  ASP.NET MVC + AdminLTE March 31, 2018 00:14 ASP.NET MVC + AdminLTE ( edit ) ASP.NET MVC Application with a Custom Layout ( Login screen ) https://code.msdn.microsoft.com/ASPNET-MVC-Application-b4b0dc3f A NuGet package for integrating Admin LTE 2.0.4 into ASP.Net MVC 5 https://github.com/eralston/AdminLteMvc  (AdminLTE version 2.0.4) ASP.NET  Example Version of Beautiful AdminLTE Template https://github.com/go2ismail/AdminLTE-ASP-NET-MVC  (AdminLTE version 2.4.0) Tutorials & Samples on adding Charts into ASP.NET MVC Applications https://canvasjs.com/docs/charts/integration/asp-net-mvc-charts/  (Chart) CRUD Operations in ASP.NET MVC Using AJAX and Bootstrap https://dzone.com/articles/crud-operation-in-aspnet-mvc-using-ajax-and-bootst AdminLTE +  ASP.NET MVC 5 + Net Framework 4.8 https://github.com/DiomedesDominguez/AdminLTE.NET ASP.NET MVC 5 Scaffolding https://github.com/robinli/MVC5-Scaffolder ASP.NET MVC 5 SmartCode Scaffolding ( HAY HAY HAY ) https://github.com/neozhu/MVC5-Scaf
Read more

Create schedule Restart SQL Server

 If you want to schedule the restart of SQL Server or Windows Server on a regular basis, you can use the Windows Task Scheduler. Here's a general guide on how to create a scheduled task for restarting SQL Server and Windows Server: Schedule SQL Server Restart: Open Task Scheduler: Press Win + R to open the Run dialog. Type taskschd.msc and press Enter. Create a Basic Task: In the Task Scheduler, select "Create Basic Task" from the right-hand actions pane. Name and Description: Provide a name and description for your task. Click Next. Trigger Configuration: Choose the trigger type (Daily, Weekly, Monthly) based on when you want the SQL Server to restart. Set the start date and time. Click Next. Action Selection: Choose "Start a program" as the action. Click Next. Program/Script: Browse and select the SQL Server service executable. The default path is typically C:\Program Files\Microsoft SQL Server\MSSQL<Instance>\MSSQL\Binn\sqlservr.exe. Add the following p
Read more

Bỏ dấu tiếng việt

     static string ConvertCombiningDiacriticsToPrecomposed(string input)     {         string normalized = input.Normalize(NormalizationForm.FormD);         StringBuilder precomposed = new StringBuilder();         foreach (char c in normalized)         {             UnicodeCategory category = CharUnicodeInfo.GetUnicodeCategory(c);             if (category != UnicodeCategory.NonSpacingMark)             {                 precomposed.Append(c);             }         }         return precomposed.ToString().Normalize(NormalizationForm.FormC);     }     static string ConvertCombiningDiacriticsToPrecomposedWithoutRemovingDiacritics(string input)     {         return input.Normalize(NormalizationForm.FormC);     }
Read more