Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
 Posted - 2014-07-15 : 14:45:51
|
| I've got thisUPDATE track_visits SET endtime = GETDATE() WHERE sessionid = @sessionidMy table track_visits also contains columns called starttime and durationWhen I do the update above how can I also set my duration column to be the number of seconds between starttime and endtime?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-15 : 14:52:03
|
| UPDATE track_visits SET endtime = GETDATE(), duration = datediff(ss, starttime, endtime) WHERE sessionid = @sessionidOr switch endtime to GETDATE() in the datediff parameters, wasn't sure which you needed there.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-07-15 : 15:34:59
|
quote:
Originally posted by Mondeo
When I do the update above how can I also set my duration column to be the number of seconds between starttime and endtime?
I suggest that you either shouldn't have a duration column or it should be a derived column. I suppose it's possible to store and index that column for some purpose, but you are really wasting space when the value can be calculated from existing columns and has the potential to get out of sync. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-07-16 : 13:04:18
|
Yes, duration should be a computed column, defined like so:ALTER TABLE track_visitsADD duration AS DATEDIFF(SECOND, starttime, endtime) You should also decide if you want to "round up" or not, like this:ALTER TABLE track_visitsADD duration AS CAST(ROUND(DATEDIFF(MILLISECOND, starttime, endtime), 4) / 1000 AS int) |
|
|
|
|
|
|
|
|
Time difference