I have a field for hours and minutes. What would be the best way of storing for exaple 72 hours? I'm using smalldatetime however I'm having problems when the value is more than 24 hours. Another example is 26 hours and 30 minutes. I also need to be able to search the data field same as date/time. Any suggestions? Thanks!
Hi Jancouver, what about this: SET NOCOUNT ONCREATE TABLE #temp_table ( hm VARCHAR(5))INSERT #temp_table VALUES('20:30')INSERT #temp_table VALUES('5:01')INSERT #temp_table VALUES('12:59')INSERT #temp_table VALUES('7:20')SET NOCOUNT OFFSELECT CONVERT(VARCHAR, SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))/3600) + ':' + CONVERT(VARCHAR, (SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))%3600)/60) FROM #temp_tableDROP TABLE #temp_table-------------------------------------------------45:50(1 Zeile(n) betroffen)
[quote user="tosc"] Hi Jancouver, what about this: SET NOCOUNT ONCREATE TABLE #temp_table ( hm VARCHAR(5))INSERT #temp_table VALUES('20:30')INSERT #temp_table VALUES('5:01')INSERT #temp_table VALUES('12:59')INSERT #temp_table VALUES('7:20')SET NOCOUNT OFFSELECT CONVERT(VARCHAR, SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))/3600) + ':' + CONVERT(VARCHAR, (SUM(DATEDIFF(ss, '1900-01-01 00:00', CONVERT(DATETIME, hm)))%3600)/60) FROM #temp_tableDROP TABLE #temp_table-------------------------------------------------45:50(1 Zeile(n) betroffen) [/quote] Hm, I can't help, but this looks familiar to me [] Anyway, if you are only concerned about storing hours and minutes one (small)int column might be sufficient. The math to derive hours and minutes from a value stored there, is trivial, it requires less storage space than a varchar(5) and for representation purposes you still can use Toscs' approach.
<P mce_keep="true">[quote user="FrankKalis"] <P>Hm, I can't help, but this looks familiar to me <IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"> </P><P>[/quote]</P><P>I'm sure, this looks to you <A href="http://www.insidesql.org/beitraege/datetime/zeitwerte-addieren">http://www.insidesql.org/beitraege/datetime/zeitwerte-addieren</A> [Y]</P>
Not sure what you are trying to do, but here is a simple way. Use an int, shift hours two places to the left and add minutes. This makes it human readable, so 72 hours and 15 minutes make the integer value 7215. To separate back to hours/minutes: for minutes take modulo 100, for hours divide by 100. @Value = @hrs * 100 + @mins; @Mins = @Value % 100; @Hrs = @Value / 100; This, of course, assumes minutes will always be in the range 0 to 59.
solution1: you say "I'm having problems when the value is more than 24 hours. Another example is 26 hours and 30 minutes" that means , that you can not use the data field same as date/time , bec format is hh:mm:ss , and 26 hour is more than one day. So as suggested by tosc, to use varchar , you can use convert(varchar, getdate(), 8) to get varchar data in format hh:mm:ss solution2: Also you can store both hour , and minutes in two different integer fields , and use datetime functions to do your process. Be sure that more than 24 hour take you to next day , as datetime , and that is not what you need. You must study carefully what the process computation and search criteria you plan to use, in order to select the best solution , and to get best performance in search.
Thanks to all for your advice. I guess I will go with one of those options (either varchar or smallint) and will convert the values on SELECT. Thanks again Jan
