CODEWITHSUNDEEP
sqlsql-server
mohamed elyamani
mohamed elyamani

Reputation: 57

Subtract Two Date Time Values in SQL Server

I want to subtract two DateTime values:

I have Start DateTime #2015-06-14 23:00:00# End Date Time #2015-06-15 01:01:00#

I want to get the duration by time format like HH:MM:SS and update it in the duration column. I tried the following code but, it doesn't work.

update [ZainJTA].[dbo].[TBL_Justification]  
set [Justification_Event_Duration]=CONVERT(Datetime,(DateDiff("n",[Justification_From],[Justification_TO])/60/24),108)

Upvotes: 2

Views: 3724

Answers (1)

Mike K
Mike K

Reputation: 486

DATEDIFF returns an INT, so dividing it by two other INTs is going to give you - another INT, most likely zero.

Try:

CONVERT(Datetime,(DateDiff(s,[Justification_From],[Justification_TO])/60.0/60/24),108)

(the 60.0 will trigger conversion to a floating point format.)

However, it'd probably make more sense to store it as a number of seconds (integer), and only do the CONVERT when you output it to display.

To display as just the time, with no day/year part, you'll also need to do a second conversion:

CONVERT(VARCHAR, CONVERT(Datetime,
    (DateDiff(s,[Justification_From],[Justification_TO])/60.0/60/24),108),108)

Upvotes: 3

Related Questions