Problem in select - 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

Problem in select

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-03-26 : 05:22:02

For some data following select return a negative value.This occur when @DockArriveAM is less than @DockDepartPM

The query return negative for the following.

DECLARE @DockArriveAM datetime
DECLARE @DockDepartPM datetime
Set @DockArriveAM='2010-03-26 14:42:21.613'
Set @DockDepartPM='2010-03-26 00:00:00.000'

SELECT
CASE WHEN LEN(CONVERT(varchar(10),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)/60)) < 2 THEN
LEFT('0'+ CONVERT(varchar(10),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)/60),2)
+ ':' +
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)%60),2)
ELSE
CONVERT(varchar(10),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)/60)
+ ':' +
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)%60),2)
END as WorkingHours

I want to replace the Negative data by '00:00' and if positive then the value return
Please help me.

Thanks & Regards
Binto Thomas

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-26 : 05:40:56

select case when @DockArriveAM < @DockDepartPM THEN '00:00' ELSE CASE WHEN LEN(CONVERT(varchar(10),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)/60)) < 2 THEN
LEFT('0'+ CONVERT(varchar(10),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)/60),2)
+ ':' +
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)%60),2)
ELSE
CONVERT(varchar(10),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)/60)
+ ':' +
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,@DockArriveAM,@DockDepartPM)%60),2)
END END as WorkingHours

binto
Yak Posting Veteran

59 Posts

Posted - 2010-03-26 : 05:50:22

Thanks for the quick reply.....

Thanks & Regards
Binto Thomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources