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 |
|
rickincanada
Starting Member
18 Posts |
 Posted - 2011-08-26 : 11:21:57
|
| I'm stuck on a problem and am hoping that someone can provide me some much needed guidance. Here's my problem:Currently in our application we're logging time stamps using the following (ridiculous) method:If a user logs on at 8:30am we stamp 8.5 (numeric(4,1)) to a table. if they log on at 1:20 PM we stamp 13.3. My question is this: is there any way for me to convert these into actual time format? My preference would be to put them back into a 12 hour clock format, so that 13.3 = 1:20 PM.Thanks for any help you can provide! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-08-26 : 12:08:42
|
| If you are using SQL 2008 or higher, just store the time in a TIME column.If you are using SQL 2005 or lower, store the time in DATETIME columns.You can format the TIME or DATETIME column to any format you want when you select it from the table.CODO ERGO SUM |
 |
|
|
rickincanada
Starting Member
18 Posts |
Posted - 2011-08-26 : 13:53:48
|
| Sorry I posted this in the wrong forum...I ended up coming up with the following solution:RIGHT('0' + RTRIM(CONVERT(CHAR(2), CAST(@time * 3600 AS INTEGER) / (60 * 60))), 2) + ':' +RIGHT('0' + RTRIM(CONVERT(CHAR(2), CAST(@time * 3600 AS INTEGER) / 60) % 60)), 2) + ':' +RIGHT('0' + RTRIM(CONVERT(CHAR(2), CAST(@time * 3600 AS INTEGER) % 60)), 2) AS 'Time'This took a time value of 8.5 and converted it to 08:30:00.Thanks for your help though. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-08-26 : 16:14:32
|
As alluded to by MVJ, if you have a chance you redesign you database, you should do it now. Storing Time like that is going to be troublesome, as you have already discovered. I'm assuming your actual code is differnet that waht you posted as that code doesn't even work. Depending on what youa re trying to do, here is a shorter version to covnert a NUMERIC to a TIME:SELECT CAST(DATEADD(Minute, @Time * 60.00, 0) AS TIME) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-08-26 : 16:25:24
|
| Peso?CODO ERGO SUM |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-08-26 : 16:41:59
|
| Opps, was confusing threads.. fixed. ;) |
|
|
|
|
|
|
|
|
Convert Integer Clock to Time