convert number into time field

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)

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

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"

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

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]

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"

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"

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"

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

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

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

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

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"

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources