CODEWITHSUNDEEP
sqlsql-server-2005
fes
fes

Reputation: 2515

Adding two timestamps in SQL

I have two fields in sql date1 varchar(4) and date2 varchar(4). The date format is just HHmm where HH is the hours and mm is the minutes.

Is there anyway in SQL which we can add the two timestamps together and work out how many hours and minutes?

e.g. 

date1 = 0230  date2 = 0145 will total 0415
date1 = 0030  date2 = 0035 will total 0105

Upvotes: 0

Views: 3112

Answers (4)

Malk
Malk

Reputation: 11983

declare @d1 varchar(4), @d2 varchar(4), @dif int

set @d1 = '0230'
set @d2 = '0145'

set @dif = (CAST(left(@d1, 2) as int) * 60) + (CAST(left(@d2, 2) as int) * 60) + CAST(right(@d1, 2) as int) + CAST(right(@d2, 2) as int)

select RIGHT('00' + cast(floor(@dif / 60) as varchar), 2) + RIGHT('00' + CAST(@dif % 60 as varchar),2)

Upvotes: 3

ramsey_tm
ramsey_tm

Reputation: 792

IF you happen to be using SQL Server you may also be able to use the DATEDIFF function described here: http://msdn.microsoft.com/en-us/library/ms189794.aspx

This will let you get the difference between any part of a datetime value. DATEADD may be of use for this as well depending on how you want to handle it.

Upvotes: 0

kasavbere
kasavbere

Reputation: 6003

Try this (use time or date time):

 select ADDTIME(time1,time2);

In mysql time is of the form

'01:02:03'

and date time is

 '2003-12-31 01:02:03'

Upvotes: 0

Brad Bonkoski
Brad Bonkoski

Reputation: 76

Not sure these are really timestamps, what not store them as integers?

Anyhow.. not sure there is a mysql function for this. You can browse: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html Not sure it will offer much help. If you store them as seconds, then you can simply have mysql add them, otherwise pull them into a script, convert them to seconds, add them together and then convert to a more readable format if desired.

Upvotes: 0

Related Questions