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 |
|
mattboy_slim
Yak Posting Veteran
72 Posts |
 Posted - 2011-07-11 : 17:20:26
|
If I have a table laid out like such:f_starttime f_endtime f_minutes7/11/2011 12:00:00 AM 7/11/2011 12:10:00 AM ?7/11/2011 12:15:30 AM 7/11/2011 12:16:00 AM ? How can a query update the f_minutes field with the minutes of difference? From my research so far, I need to use DATEDIFF, but I cannot seem to figure out the proper query to do the update. Rather than show my absolute ignorance and show you what I have tried, I'll instead ask for assitance in the proper way to accomplish this.Thanks in advance,Matt |
|
|
pduffin
Yak Posting Veteran
68 Posts |
Posted - 2011-07-11 : 21:30:52
|
| Here you go!update <table name>set f_minutes = datediff(mi,f_starttime,f_endtime) |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-11 : 22:09:34
|
| Instead of creating a column for this value, you should just put the calculation in the query. By storing the value, if either the starttime or endtime are updated - the value will be wrong.The other option, if you really want it in the table is to use a computed column instead. |
|
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2011-07-12 : 09:35:41
|
| Thanks pduffin, that is far less complicated than I was making it. That works like a champ. jeffw8713, I understand what you are saying. However, with the possibility for hundreds of thousands of records queried, my goal was to run a SQL job every x minutes to calculate this, so that that when a large query was run it wouldn't have to work as hard and would speed up the process. The records are not "updatable" so to speak, as there will be no "update" function in the front-end interface for these records.UPDATE: Is there any way to make it return minutes with two decimal places? The field supports it, but the query only returns integers.Thanks so much for the help folks,Matt |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2011-07-12 : 10:10:36
|
Matt, have you considered the implications of running an UPDATE statement that updates every single row in the table in terms of lock escalation and blocking? It is very likely that all reads will be blocked while this query is executing, so I hope it runs in a few milliseconds. As for formatting the output of the query, this is far better handled at the front-end, where all presentation issues and dressing-up should be handled anyway. If you really want to do this, you can cast the output column to decimal. Like this:USE Northwind GOselect CAST(categoryid as decimal(9,2)), CategoryID, CategoryName from Categories Keep in mind that the DATEDIFF function returns an INT, so it will only return whole numbers. I hope the seconds are not important to your query, otherwise a better approach would be store the difference in seconds and convert to minutes while querying.OS |
|
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2011-07-12 : 10:23:17
|
Thanks for the information...good knowledge to have. Here is the query that updates properly:Set f_runtime = datediff(ss, [f_starttime], [f_endtime])/60.0So the updated table looks like this:f_starttime f_endtime f_minutes7/11/2011 12:00:00 AM 7/11/2011 12:10:00 AM 10.007/11/2011 12:15:30 AM 7/11/2011 12:16:00 AM 0.50 |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-12 : 16:06:23
|
| It would be a lot easier using a computed column and persisting that column instead of running updates every few minutes. Even with the calculation in the query itself - you should not see a significant performance hit unless that column is used in the where clause to filter results.By persisting the column, you will affect the performance of inserts and updates - but it should not be an issue.I would not recommend using an update every few minutes - that will either not be quick enough and users will not have the value when they need it, or it will cause significant blocking that will cause performance issues. |
|
|
|
|
|
|
|
|
Minutes Between two DateTime Fields