Datediff()

[Socarsky]

How can I get Datediff (mi, 'here already in sql as datetime', 'I want to insert here with one query of Datediff' )
I mean start datetime in sql already in but end part not yet and Iinsert it with one the same query that I add Datediff to another field as individually.
Is it possible?

 

[IanRyder]

Hi,

I have no idea what you are asking here so why not start with having a look at how the DateDiff function works in SQL Server. i.e:-

SQL Server DATEDIFF() Function

Now that you have this as a basis to work from, please try and explain exactly what it is that that you are trying to accomplish for us to help you further.

Cheers,

Ian

 

[jmcilhinney]

Either you're saying that you want to get the difference between two dates in the SQL Server or you want to get the difference between two dates in VB. If it's the former then you should do as IanRyder suggests. If it's the latter then the fact that one of the values originates from SQL Server is irrelevant. You query the database to get the value in the same way as you always do and, once you've done that, you have a .NET DateTime. Once you have a .NET DateTime, where it came from is of not relevance to how you use it.

If you want to get the difference between two DateTimes then don't use DateDiff unless you want a unit greater then days. If you want months or years then use DataDiff but, otherwise, just subtract one DateTime from the other and use the resulting TimeSpan. I don't recall exactly but presumably "mi" is for minutes or milliseconds. The TimeSpan structure has TotalMinutes and TotalMilliseconds properties.

 

[Socarsky]

I tried below in SQL query that says query executed successfully but return zero value, I think of course we should give timedate which is right form for DATEDIFF.

SELECT DATEDIFF(minute, dDateRun, dDateQuit) from tbTheProgramRuns WHERE nRecordID = -32768

BTW, that style for vb.net codes.. I use below in my project :

Dim SqlUpdate As String = "UPDATE tbTheProgramRuns SET dDateQuit = " & getdatetimeForQuit & ", dMinuteDiff = (SELECT DATEDIFF(minute, dDateRun, dDateQuit)), nDataSaved = 1 WHERE nRecordID = _
(SELECT nRecordID FROM tbTheProgramRuns WHERE nRecordID = " & getReturnRecordID & ")"
            Dim SqlCmdUpdate As New SqlCommand(SqlUpdate, sConn)
            SqlCmdUpdate.ExecuteNonQuery()

My project when runs it automatically insert its running datetime to SQL DB and when the program quit then updates dDateQuit so far so good but I wanted to get datetime difference to update in a field then my issue at that time occurred. But I think I will change sql query script with an extra transaction to update datetime difference.

Sorry for low details at the first creating this topic.

 

[IanRyder]

Hi,

My project when runs it automatically insert its running datetime to SQL DB and when the program quit then updates dDateQuit so far so good but I wanted to get datetime difference to update in a field then my issue at that time occurred.

The way you are using the DataDiff function at the moment will not work since at the point you run the query the dDateQuit field will either be a null or empty value since it has not yet been updated as part of the Update query. The way to do this is to use something that currently contains a value such as CURRENT_TIMESTAMP and then use that in both of the fields to be updated. i.e:-

UPDATE tbTheProgramRuns Set dDateQuit = CURRENT_TIMESTAMP, dMinuteDiff=DATEDIFF(Minute, dDateRun, CURRENT_TIMESTAMP) Where IDNo=1

In addition to this, that seems like a bit of an overly complicated way to select the record to be updated since you should just be saying nRecordID = getReturnRecordID.

Hope that helps.

Cheers,

Ian

 

[Socarsky]

The way you are using the DataDiff function at the moment will not work since at the point you run the query the dDateQuit field will either be a null or empty value since it has not yet been updated as part of the Update query. The way to do this is to use something that currently contains a value such as CURRENT_TIMESTAMP and then use that in both of the fields to be updated. i.e:-

Thanks IanRyder, after your helping now the program perfectly works well.