Calculate time difference between two times in hrs and minutes

Question

1.00/5 (1 vote)

See more:

Hi All,
I need to calculate time difference between two times.The times with AM and PM are taken from two dropdownlists .It is working correctly in case of hours.But when it comes to 30 mins difference its not working.

For example :

Start time : 10:30 AM
End Time : 11:00 AM

O/P : 0:30

How to achieve this .Any help will be really appreciated.Thanks in Advance

What I have tried:

int.Parse(objcon.GetDataReadervalue("select datediff(HOUR,'" + ddl_fromtime.SelectedValue.ToString() + "','" + ddl_totime.SelectedValue.ToString() + "') ", "Value"))

Posted 5-Jul-18 1:32am

Member 12926744

Updated 5-Jul-18 2:21am

Add a Solution

Comments

CHill60 5-Jul-18 7:45am

As per your earlier question - change HOUR to MINUTE and change the rest of the code appropriately - and don't use string concatenation to create SQL queries!

CHill60 5-Jul-18 7:48am

Another point - why are you using SQL to calculate the date difference instead of the C# functions?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-07-05T02:02:00

Solution 2

First off: don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

When you have fixed that throughout your app, you can work on this problem, which is pretty trivial: do the DATEDIFF in terms of minutes, not hours. You can then work that into hours and minutes (if you need to, probably you don't) using divide and modulus 60.

Posted 5-Jul-18 2:02am

OriginalGriff

Comments

Mac McCall 6-Nov-21 15:23pm

Here is a VB class that will let you build parameters and use them in your queries. To use this class, copy it into your project then create a new instance like this:
sqlControl = New SQL Control
There is a default connection string or you can use the override to create you own.

Now when you need to create a SQL statement, create parameters like this:
sqlControl.AddParam("@name", data)
Do this for each data item you need to send to the database.

Finally create and execute you SQL statement like this:
sqlControl.ExecQuery("Select Name from Names where Name = @name"
This returns a datatable called sqlControl.DBDT that will contain the results of your query.

PS: this class can be easily translated to C# if needed.

* Not my code. I found it on YouTube. It's lightweight and fast.

Imports System.Text
Imports System.Data.SqlClient
Imports System.Diagnostics.Eventing.Reader

Public Class SQLControl
Private DBCon As New SqlConnection("server=localhost\SQLEXPRESS;database=HomeDepot;Trusted_Connection=True; ")

'Private DBCon As New SqlConnection("server=localhost\SQLEXPRESS;database=database2;User=database2;Pwd=Password")
Private DBCmd As SqlCommand
Private HasRecords As Boolean = False

' DB Data
Public DBDA As SqlDataAdapter
Public DBDT As DataTable

' Query Parameters
Public Params As New List(Of SqlParameter)

' Query Statistics
Public RecordCount As Int32
Public Exception As String

Public Sub New()

End Sub

' Allow connection string override
Public Sub New(ConnectionString As String)
DBCon = New SqlConnection(ConnectionString)
End Sub

' Execute Query Sub
Public Sub execQuery(Query As String)
' Reset Query Stats
RecordCount = 0
Exception = ""

Try
DBCon.Open()

' Creat DB Command
DBCmd = New SqlCommand(Query, DBCon)

' Load Params into DB Command
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

' Clear Param List
Params.Clear()

' Execute command and fill datatable
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)
DBCon.Close()
Catch ex As Exception
' Capture Error
Exception = "ExecQuery Error: " & vbNewLine & ex.Message

Finally
' Close connection
If DBCon.State = ConnectionState.Open Then
DBCon.Close()
End If
End Try
End Sub

' Add Params
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New SqlParameter(Name, Value)
Params.Add(NewParam)
End Sub

Public Function HasException(Optional Report As Boolean = False) As Boolean
If String.IsNullOrEmpty(Exception) Then Return False
If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
Return True
End Function

End Class

Bimal pedini · Accepted Answer · 2018-07-05T02:21:00

you could try his code this might help you out.

var date1 = DateTime.Now.AddMinutes(30);  // it will add 30 minutes of time of the current time 
            var date2 = DateTime.Now;
            var diff = date1 - date2;
            var diffMinutes = date1.Minute - date2.Minute;
            Console.WriteLine("date difference is : " + diff); 
            Console.WriteLine("Minutes difference is : " + diffMinutes); 
            Console.ReadLine();