July 31, 2014 at 8:50 am
Hello,
I am having some issues converting time to decimal from one of my column which display it as
2014-07- 08:16:31.000 (datetimecreated)
A solution to make this work would be greatly appreciated.
Thank you,
July 31, 2014 at 9:03 am
What precise date and time does that represent? Is i6 16:31:00 on 8th July 2014, or is it 08:16:31 on some day in July 2014?
John
July 31, 2014 at 9:07 am
Sorry typor error.
2014-07-29 08:16:31.000
July 31, 2014 at 9:09 am
OK, now why do you want to convert to decimal? What should the result look like for that date and time?
John
July 31, 2014 at 9:18 am
Sql server stores datetime as decimal. You can easily cast a datetime to decimal. But what is the point? What are you trying to do with it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2014 at 9:44 am
The goal here is to calculate a UPH of an activity, the activity is considered picking materials.
The issues I am running into is that as long the users are continuously performing the picking without interruption I can calculate their UPH based on the total units picked divided by the hour but when I have a user who only performed a single activity for the hour it throws my numbers off. I believe by converting the time to decimal this will eliminate the issue.
Based on that I am expecting a (8.266666667) Result number.
Thanks,
July 31, 2014 at 9:47 am
You can use DATEPART to get the hours, minutes, seconds and milliseconds components from the date, and build your decimal number from there.
John
July 31, 2014 at 9:49 am
Not sure what decimal you are actually after, but here are some examples of what you might be after.
select
a.CurrentDatetime,
a.CurrentTimeOfDay,
HourOfDay=
convert(numeric(18,4),round(datediff(ms,0,a.TimeOfDay)/3600000.000000,4)),
MinuteOfDay=
convert(numeric(18,4),round(datediff(ms,0,a.TimeOfDay)/60000.000000,4)),
SecondOfDay=
convert(numeric(18,4),round(datediff(ms,0,a.TimeOfDay)/1000.000000,4)),
MillisecondOfDay=
datediff(ms,0,a.TimeOfDay)
from
(
selectCurrentDatetime= getdate(),
CurrentTimeOfDay= convert(time(3),getdate()),
TimeOfDay= convert(datetime,convert(time,getdate()))
) a
Results:
CurrentDatetime CurrentTimeOfDay HourOfDay MinuteOfDay SecondOfDay MillisecondOfDay
----------------------- ---------------- --------- ----------- ----------- ----------------
2014-07-31 11:47:15.243 11:47:15.243 11.7876 707.2541 42435.2430 42435243
July 31, 2014 at 9:51 am
Could you provide an example please?
Thanks,
July 31, 2014 at 10:00 am
ba.saidou (7/31/2014)
Could you provide an example please?Thanks,
An example of what?
You haven't really explained your problem well enough for anyone to know what you are asking, and you haven't explained the result you are after.
July 31, 2014 at 10:00 am
ba.saidou (7/31/2014)
The goal here is to calculate a UPH of an activity, the activity is considered picking materials.The issues I am running into is that as long the users are continuously performing the picking without interruption I can calculate their UPH based on the total units picked divided by the hour but when I have a user who only performed a single activity for the hour it throws my numbers off. I believe by converting the time to decimal this will eliminate the issue.
Based on that I am expecting a (8.266666667) Result number.
Thanks,
So what you really want is to look at the time portion. Keep the hours as is and make the minutes a decimal? Seems pretty strange but whatever.
This should do it.
declare @UPH datetime = '2014-07-29 08:16:31.000'
select cast(DATEPART(hour, @UPH) as varchar(2)) + '.' + stuff(cast(DATEPART(minute, @UPH) / 60.0 as varchar(10)), 1, 2, '')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2014 at 10:34 am
Thanks Sean it worked!!!
July 31, 2014 at 10:35 am
It sounds to me like you're after the number of units picked per hour for a specific period of time. It reminds me of a shop floor productivity report. Is this correct? If so, simply subtract the times and divide the quantity by the difference. This example uses the difference in minutes for precision.
with times as (
select dateadd(minute, -90, getdate()) starting_time,
GETDATE() ending_time,
14 quantity
union all
select dateadd(hour, -2, getdate()) starting_time,
GETDATE() ending_time,
160 quantity)
select starting_time, ending_time, quantity,
convert(numeric(12, 6), DATEDIFF(minute, starting_time, ending_time)) / 60 hours,
round(quantity / convert(numeric(12, 6), DATEDIFF(minute, starting_time, ending_time)) * 60, 3) uph
from times;
Am I over-simplifying this?
July 31, 2014 at 11:29 am
Hello Ed,
You nailed and allowed me to go beyound expectation.
Thank you!!!
July 31, 2014 at 11:32 am
No problem. Glad I was able to help. Thanks for the feedback.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply