Log in

View Full Version : Any SQL Guru's out there?

Moonliner
10-25-2010, 01:01 PM
I'm spacing on this one....

I have a SQL table with the time stored as the number of milliseconds since midnight (I think). How do I get from 5,677,528 back to 11:16am?
Tref
10-25-2010, 01:15 PM
I'm spacing on this one....

I have a SQL table with the time stored as the number of milliseconds since midnight (I think). How do I get from 5,677,528 back to 11:16am?

I would love to help you, but, hell, I wish I could get back to 11:16 am! Those were so some good times.
Disneyphile
10-25-2010, 02:10 PM
Well, let me pry open a box that's been stored in the back of my brain for 4 years. I recall it being a very simple code fix...

I think it's something like dbo.FormatDateTime('SHORTDATE')

Lemme know if that sets you on the right track, and if not, I'll open one of my old tables. :)
Capt Jack
10-25-2010, 03:00 PM
I'm spacing on this one....

I have a SQL table with the time stored as the number of milliseconds since midnight (I think).

Id be most curious as to why such a thing would be done to begin with. Is this a table you built?
BarTopDancer
10-25-2010, 03:32 PM
select * from dbo.coworkers
where coworkers = 'helpful'


2 results returned.
Moonliner
10-25-2010, 04:20 PM
Is this a table you built?

Oh hell no! It's a commercial product from a company that ought to know better.

I found what I needed. After checking my math I figured out the values where 100's of a second not milliseconds. However SQL does not seem to have a centisecond option so I ended up converting to milliseconds anyways.


rtrim(substring(Convert(varchar,dateadd(ms,time*10 ,'2010-10-2'),13),13,5))

did the trick.
Cadaverous Pallor
10-25-2010, 04:56 PM
My husband must be in meetings all day.
Ghoulish Delight
10-25-2010, 06:33 PM
I could have done the math, but still don't have enough stick time with sql to have the syntax down without a lot of reference/trial and error. And I knew Moonie could do the math, so the syntax was the important part.
Kevy Baby
10-25-2010, 07:19 PM
Any SQL Guru's out there?Yes, I am sure that there are a lot of them.
Alex
10-25-2010, 07:55 PM
I could have done the math, but still don't have enough stick time with sql to have the syntax down without a lot of reference/trial and error. And I knew Moonie could do the math, so the syntax was the important part.

Same here. Started to respond with math and said "no, that can't be the problem."