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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 convert number into time field

Author  Topic 

cardgunner

326 Posts

Posted - 2008-08-08 : 16:31:38
There is an int field that may contain a time.

It has a number such as 59073 and the field description is time created.

Can you convert that number to a anything that resembles a time?

I tried cast(59073 as datetime)

and cast(59073 as smalldatetime)

and cast(59073 as timestamp)

but nothing that resenbles a time such as 08:24:38.263

Any ideas?

CardGunner

pootle_flump

1064 Posts

Posted - 2008-08-08 : 16:47:43
Hi

What is the equation for this number? If no. of seconds past midnight then:
SELECT cast(0.59073 as datetime)
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-08-08 : 16:58:29
quote:
Originally posted by pootle_flump

Hi

What is the equation for this number? If no. of seconds past midnight then:
SELECT cast(0.59073 as datetime)



Good question. I have no idea. The field is in my table with a description of time created.

There is date field before that is recording the date but no time.

So If that number is seconds past midnight I would then have to convert(cast) the number and somehow add it to the date field to get an actual datetime record.

Nothing is easy I guess.

Let me see if in fact the conversion you suggested is even repmotly close to the time the record was created.

Thank you for the help.

CardGunner
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-08 : 18:02:22
Seconds since midnight?
SELECT cast(59073 / 86400.0 as datetime)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-08-08 : 19:24:17
quote:
Originally posted by Peso

Seconds since midnight?
SELECT cast(59073 / 86400.0 as datetime)



E 12°55'05.25"
N 56°04'39.16"




I'm not at work now.

What is the result of
SELECT cast(59073 / 86400.0 as datetime)
?



CardGunner
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-08 : 23:13:47
SELECT cast(59073 / 86400.0 as datetime)

RESULT : 1900-01-01 16:24:32.990



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-09 : 01:01:20
As good as any other guess since OP doesn't know what 59073 is supposed to represent.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-09 : 01:01:21
As good as any other guess since OP doesn't know what 59073 is supposed to represent.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-09 : 01:04:19
SELECT DATEADD(DAY, 59073, '18461113')



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-08-09 : 08:44:13
quote:
Originally posted by Peso

SELECT DATEADD(DAY, 59073, '18461113')



Sorry, What is the results of this. I'm not at work and my computer at work is shut off.

I'm tempted to go in and turn it on.

I looked in the front end, before I left, and saw that the time was 15(h) and I can't remember the rest, I think.

Also what does 18461113 represent?

CardGunner
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-08-09 : 08:49:07
quote:
Originally posted by Peso

Seconds since midnight?
SELECT cast(59073 / 86400.0 as datetime)




What does 86400 represent?

CardGunner
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-09 : 08:53:59
quote:
Originally posted by cardgunner

quote:
Originally posted by Peso

Seconds since midnight?
SELECT cast(59073 / 86400.0 as datetime)




What does 86400 represent?

CardGunner


select 24*60*60

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-08-11 : 08:35:31
quote:

select 24*60*60



Back At work
results are
1900-01-01 16:24:32.990
.

From what I can see from the front end that is it!!!

Now all I have to do is add that time to the created date of '2007-03-29-00:00:00.000' and I will be in business.

So the result would be '2007-03-29-16:24:32.990'.

I'll take a look and try to figure it out for myself first.

I'm assuming 24*60*60 = 24 hours * 60 min * 60 sec.

Thank you Madhivanan

CardGunner
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 08:41:07
Good work Madhi!
SELECT	CAST(59073 / 86400.0 as datetime) + CreatedDateColumnNameHere
FROM YourTableNameHere



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-08-11 : 08:41:14
Oops Sorry,

Thank you Peso for
SELECT cast(59073 / 86400.0 as datetime)
.

That appears to be the right one.

CardGunner
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 08:42:12
select created_Date+cast(59073 / 86400.0 as datetime) from table

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-08-11 : 09:00:40
Thanks.

I could have figured that out myself though

You guys are too fast and too good!

CardGunner
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 09:05:00
quote:
Originally posted by cardgunner

Thanks.

I could have figured that out myself though

You guys are too fast and too good!

CardGunner




Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -