Datetime problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Datetime problem

Author

Topic

Asdzxc1986
Starting Member

14 Posts

Posted - 2009-11-18 : 03:28:43

Have a simple formula:
Time_Unload=Quantity*Time_for_one_thing;

Quantity float
Time_for_one_thing datetime

I dont know how to convert float to datetime.

Example:
I have 4,5 boxes, and 15 minutes time for unloading per box.
as a result I have to get 4,5*15min = 67,5 min or 1 hour 7 min 30 sec.

I will be thankful

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-18 : 03:52:00

Post table structure, sample data and expected result

Madhivanan

Failing to plan is Planning to fail

Asdzxc1986
Starting Member

14 Posts

Posted - 2009-11-18 : 04:30:05

table Orders

Id int primary key
...
Quantity float
Time_for_one_thing datetime
Time_Unload datetime
...

I need to fill time_unload column for every order.
I have already algorythm how to do it. I need only convert expression 4,5*15min to 1 hour 7 min 30 sec.

Do you need any other information?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-18 : 04:37:52

looks like your Time_for_one_thing is in minute. Then you should just use integer data type and not datetime

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

Asdzxc1986
Starting Member

14 Posts

Posted - 2009-11-18 : 05:02:03

You right. I can use int for Time_for_one_thing, but Time_unload I can't change. It should be datetime. So expression like 4,5*15 I have to convert to 1 hour 7 min 30 sec

quote:
Originally posted by khtan

looks like your Time_for_one_thing is in minute. Then you should just use integer data type and not datetime

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-18 : 05:17:23

assuming the Time_for_one_thing is integer


select Time_unload = dateadd(minute, Quantity * Time_for_one_thing, 0)
from   yourtable

another question, any particular reason quantity is float ? Are you expecting decimal places in quantity ?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-18 : 05:22:10

So timepart is needed only in a datetime field.
I have set the datepart to 19000101 because it is not interesting...


declare @Quantity float
declare @Time_Unload datetime
declare @Time_for_one_thing int
set @Time_for_one_thing = 15 -- minutes
set @Quantity = 4.5

select dateadd(second,(@Quantity * @Time_for_one_thing) * 60, '19000101')

gives: 1900-01-01 01:07:30.000

No, you're never too old to Yak'n'Roll if you're too young to die.

Asdzxc1986
Starting Member

14 Posts

Posted - 2009-11-18 : 05:38:16

webfred, khtan thanks. It works!

2khtan: unfortunately quantity should be float.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-18 : 05:58:22

welcome