Need help converting smallints to datetime

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

Need help converting smallints to datetime

Author

Topic

RalphWiggum
Starting Member

13 Posts

Posted - 2011-09-16 : 09:37:05

I have a table:

CREATE TABLE [dbo].[E_Schedules](
[CLIENT] [nvarchar](8) NOT NULL,
[COMPANY] [nvarchar](3) NOT NULL,
[S_ID] [bigint] IDENTITY(1,1) NOT NULL,
[S_EMPNO] [nvarchar](9) NOT NULL CONSTRAINT [DF__E_Schedul__S_EMP__4E7E8A33] DEFAULT ('<default>'),
[S_DATE] [datetime] NOT NULL CONSTRAINT [DF__E_Schedul__S_DAT__4F72AE6C] DEFAULT (getdate()),
[S_START] [smallint] NOT NULL CONSTRAINT [DF__E_Schedul__S_STA__5066D2A5] DEFAULT ((0)),
[S_STOP] [smallint] NOT NULL CONSTRAINT [DF__E_Schedul__S_STA__5066D2A5] DEFAULT ((0)),
[S_LUNMIN] [smallint] NULL CONSTRAINT [DF__E_Schedul__S_LUN__5DC0CDC3] DEFAULT ((0)),
CONSTRAINT [PK_E_Schedules] PRIMARY KEY CLUSTERED
(
[CLIENT] ASC,
[COMPANY] ASC,
[S_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I need a query that gives me "total hours worked", which is essentially ((s_stop-s_start) - s_lunmin). The result needs to be visible as x.xx hours (8.00, for example).

However, when you do the math on smallint, you'll see something like this:
1200 (s_stop)
600 (s_start)
30 (s_lunmin)
((1200 - 600) - 30) = 570, but should be 5.5 total hours worked.

What logic am I missing to make 570 look like 5.5?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 09:46:01

Hello,

When performing operations on mixed datatypes, the lowest precision will be output (in this case smallint). So the values (s_stop, s_start, and s_lunmin) must be cast to the intended output datatype.

hth.

RalphWiggum
Starting Member

13 Posts

Posted - 2011-09-16 : 09:49:57

quote:
Originally posted by ehorn

Hello,

When performing operations on mixed datatypes, the lowest precision will be output (in this case smallint). So the values (s_stop, s_start, and s_lunmin) must be cast to the intended output datatype.

hth.

Hi,

I guess I should have renamed the thread, as DATETIME doesn't really play into the math -- the operation is done on 3 fields that are the same datatype. It's just that I can't think of the logic that will show 5.5 as opposed to 570

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-09-16 : 10:25:22

[code]
DECLARE @S_Start smallint, @S_Stop smallint, @S_LunMin smallint, @Mins smallint;
SELECT @S_Start = 600, @S_Stop = 1200, @S_LunMin = 30;

SELECT (@S_Stop/100 * 60 + @S_Stop%100 - @S_Start/100 * 60 - @S_Start%100 - @S_LunMin/100 * 60 - @S_LunMin%100) / 60.0
[/code]

RalphWiggum
Starting Member

13 Posts

Posted - 2011-09-16 : 10:55:53

quote:

Originally posted by Ifor


DECLARE @S_Start smallint, @S_Stop smallint, @S_LunMin smallint, @Mins smallint;
SELECT @S_Start = 600, @S_Stop = 1200, @S_LunMin = 30;

SELECT (@S_Stop/100 * 60 +  @S_Stop%100 - @S_Start/100 * 60 -  @S_Start%100 - @S_LunMin/100 * 60 -  @S_LunMin%100) / 60.0

MODULO! Sweet lord -- I can think of at least 3 other stored procs where that will cure a headache. I constantly forget about that operator. Thanks, Ifor!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources