Attempting to get a time - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Attempting to get a time

Author

Topic

CDBanks
Starting Member

23 Posts

Posted - 2005-10-18 : 12:12:18

I have a table that is replicated from one of our applications that include time punch information. Unfortanately, they have the in and out times in the following formats:
InHour, InMinute, OutHour, OutMinute, TotalMinutes, TotalHours
We are a restaurant company and I would like to look at this information by day part. For example: Stage 1 Between 6AM and 11AM, Stage 2 from 11AM to 10PM & Stage 3 10PM to 6AM. One of the problems I am having is creating the InTime as InHour + (InMinute/60). I think this is partially because InMinute is set to data type Int 4. Is there any other ways to convert this information that would be easy for a novice to follow?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-18 : 12:23:23

If the problem is that you are getting InTime values truncated to whole hours, and you want to include portions of the hour, then try this:
InTime as InHour + (InMinute/60.00)
You may also find the DATEADD() function usefull.

CDBanks
Starting Member

23 Posts

Posted - 2005-10-18 : 12:38:22

When I attempt the equation.
InTime as InHour + (InMinute/60.00)
the values are truncated to the hour. I thought about creating a table that had all the time conversions included and then using the formula InHour*60+InMinute but thought there must be an easier way.

I'm not sure how I would use the DATEADD() function.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-18 : 13:03:18

[quoteWhen I attempt the equation.
InTime as InHour + (InMinute/60.00)
the values are truncated to the hour.[/quote]
No, it does not. Did you try running the code?

declare	@InHour smallint
declare	@InMinute smallint
set	@InHour = 10
set	@InMinute = 17

select	@InHour + (@InMinute/60) as InTime --Truncates to hour
select	@InHour + (@InMinute/60.00) as InTime --Displays portions of hour

CDBanks
Starting Member

23 Posts

Posted - 2005-10-18 : 14:15:51

Thanks, my bad. I missed the 60.00 and was using 60 instead. I hate when things are answered that easily.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 02:17:13

60.00 implicitly convert the result to be of Float than Integer

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources