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 |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2007-11-02 : 13:26:09
|
| hi,how can I work out how many counts a customer has in hours and minutes ?eg, a customer has 30 counts in 23300 seconds, how can I show that as hours and minutes ?thank you. |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-02 : 13:35:34
|
| you can use something similar to this...select CONVERT(char(10), dbo.sometable.somedatetime, 121) AS [DAY] ,CONVERT(char(2), dbo.sometable.somedatetime, 108) AS [MILITARY_HOUR] ,RIGHT(CONVERT(char(5), dbo.sometable.somedatetime, 108), 2) AS [MINUTE] ,count(*) AS [COUNT]from dbo.sometablegroup by CONVERT(char(10), dbo.sometable.somedatetime, 121) ,CONVERT(char(2), dbo.sometable.somedatetime, 108) ,RIGHT(CONVERT(char(5), dbo.sometable.somedatetime, 108), 2) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-02 : 13:38:22
|
"23300 seconds, how can I show that as hours and minutes "SELECT [Hours] = (23300 / 60) / 60, [Minutes] = (23300 / 60) % 60 Kristen |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-11-02 : 15:46:01
|
You could also use DateTime functions:SELECT DATEPART(HOUR, DATEADD(SECOND, 23300, 0)), DATEPART(MINUTE, DATEADD(SECOND, 23300, 0)) |
|
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-11-02 : 17:19:34
|
| http://www.lazydba.com/I have found some good answer at this Web. Good Luck!!! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-03 : 06:55:00
|
Good work!Because all other suggestions does the job too...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-03 : 08:47:26
|
quote:
Originally posted by osirisa
http://www.lazydba.com/I have found some good answer at this Web. Good Luck!!!
Can you post that good answer here?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
convert seconds to hours and mins