plz....help..time format - 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

plz....help..time format

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2008-09-30 : 21:26:54

timin = 7:00 pm
timout = 5:00 am
select datediff(hh,timin,timout) as No of Overtime from Overtime
i want to see the difference in the standard format
12 hours not 24 hours
thank you in advance.....
please help im having difficulty of formatting the time....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 23:55:48

didnt understand significance of 12/24 hrs here. what you're showing is just difference between two date values. this will be always in hours

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-01 : 03:12:06

quote:
Originally posted by wormz666

timin = 7:00 pm
timout = 5:00 am
select datediff(hh,timin,timout) as No of Overtime from Overtime
i want to see the difference in the standard format
12 hours not 24 hours
thank you in advance.....
please help im having difficulty of formatting the time....

Where do you want to show formatted times?

Madhivanan

Failing to plan is Planning to fail

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-02 : 05:41:20

i want to know the difference between 5:00 PM to 5:00 AM

7:00 PM = the standard value is 19:00 hr
im developing a payroll system on my project my problem is on overtime period. f my overtime start on 7:00 PM and i finish at 4:00 AM....

i want to know the range of his/her overtime....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 05:43:39

7pm is 19:00.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 05:44:34

quote:
Originally posted by wormz666

i want to know the difference between 5:00 PM to 5:00 AM

7:00 PM = the standard value is 18:00 hr
im developing a payroll system on my project my problem is on overtime period. f my overtime start on 7:00 PM and i finish at 4:00 AM....

i want to know the range of his/her overtime....

that case best way is store datepart as well along with time. then you can simply apply datediff function to get the range. without date part it will be difficult to get range for cases where it stretches between days.

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-02 : 05:46:25

select (empmas.lname + ',' + empmas.fname + ' ' + empmas.mname) as [Name],sum(datediff(hh,dtr.timin,dtr.timout)) as [No of Hours],
position.salary,((sum((datediff(hh,dtr.timin,dtr.timout)))/8)*position.salary) as Amount,
(((sum((datediff(hh,dtr.timin,dtr.timout)))/8)*position.salary) * .02) as cola,
datediff(hh,overtimetrs.otimout,overtimetrs.otimin) as [overtime hours]
from dtr inner join((empmas inner join(empdet inner join position on empdet.posno=position.posno) on empmas.empno=empdet.empno)
inner join overtimetrs on empmas.empno=overtimetrs.empno)
on dtr.empno=empmas.empno
group by empmas.lname,empmas.fname,empmas.mname,position.salary,overtimetrs.otimout,overtimetrs.otimin

plz... help me.....
the difference of datediff is incorrect...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 05:46:37

see this article which explains working with time spans

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 05:50:11

quote:
Originally posted by wormz666

select (empmas.lname + ',' + empmas.fname + ' ' + empmas.mname) as [Name],sum(datediff(hh,dtr.timin,dtr.timout)) as [No of Hours],
position.salary,((sum((datediff(hh,dtr.timin,dtr.timout)))/8)*position.salary) as Amount,
(((sum((datediff(hh,dtr.timin,dtr.timout)))/8)*position.salary) * .02) as cola,
datediff(hh,overtimetrs.otimout,overtimetrs.otimin) as [overtime hours]
from dtr inner join((empmas inner join(empdet inner join position on empdet.posno=position.posno) on empmas.empno=empdet.empno)
inner join overtimetrs on empmas.empno=overtimetrs.empno)
on dtr.empno=empmas.empno
group by empmas.lname,empmas.fname,empmas.mname,position.salary,overtimetrs.otimout,overtimetrs.otimin

plz... help me.....
the difference of datediff is incorrect...

why? can you explain with some sample data of case where you think its incorrect? without seeing sample data its hard to understand the problem

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-02 : 06:43:31

incorrect there is the output value..when my overtime start on 7:00 PM and end 5:00 AM
when i want to find the difference of the start and end is 14..which is incorrect
the correct output it return value on 10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 06:48:13

quote:
Originally posted by wormz666

incorrect there is the output value..when my overtime start on 7:00 PM and end 5:00 AM
when i want to find the difference of the start and end is 14..which is incorrect
the correct output it return value on 10

thats because you're not storing date along with it.so it cant understand whether they are times in same day or different day. thats why i suggested you to store day info also along with time.
B/w did you have a look at link i posted. it explains how to tackle with time span scenarios.

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-02 : 06:51:29

start = 10/02/2008 19:00:00
end = 10/03/2008 05:00:00

the value it return is incorrect.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 06:52:28

[code]DECLARE @Start DATETIME,
@End DATETIME

SELECT @Start = '7PM',
@End = '5AM'

SELECT @Start AS Start,
@End AS [End],
CASE
WHEN @Start > @End THEN DATEDIFF(HOUR, @Start, @End + 1)
ELSE DATEDIFF(HOUR, @Start, @End)
END AS Hours[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 06:56:22

quote:

Originally posted by Peso

DECLARE	@Start DATETIME,
	@End DATETIME

SELECT	@Start = '7PM',
	@End = '5AM'

SELECT	@Start AS Start,
	@End AS [End],
	CASE
		WHEN @Start > @End THEN DATEDIFF(HOUR, @Start, @End + 1)
		ELSE DATEDIFF(HOUR, @Start, @End)
	END AS Hours

E 12°55'05.63"
N 56°04'39.26"

but what happens if you've some thing like below?
SELECT @Start = '7PM',
@End = '9PM'

but which really means shift ended on next day 9?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 07:00:32

I get two hours as result.
Or you are referring to a worker that work for 26 hours straight?

You can only get 2 hours since the two dates are on the same day.
If you use OP more precise datetime values as this

SELECT	@Start = '20081002 7PM',
	@End = '20081003 9PM'

you only need DATEDIFF(HOUR, @Start, @End)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 07:11:08

quote:
Originally posted by Peso

I get two hours as result.
Or you are referring to a worker that work for 26 hours straight?

You can only get 2 hours since the two dates are on the same day.
If you use OP more precise datetime values as this
SELECT @Start = '20081002 7PM',
@End = '20081003 9PM'
you only need DATEDIFF(HOUR, @Start, @End)
E 12°55'05.63"
N 56°04'39.26"

yup. Thats why i told its better to keep the date also with time.
Once we had a project for installations dept where engineers used to work onsite for many days continuos. such cases there were instances of shifts like above. so we had to take date also along with time for getting duration.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 07:16:35

Many days?
You don't sleep in India?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 07:24:06

quote:
Originally posted by Peso

Many days?
You don't sleep in India?

E 12°55'05.63"
N 56°04'39.26"

Not me
the engineer's in our clients dept
They consider the time he goes from office till he returns the office as total time taken for installation. which involes travel time, time at onsite which might invlove multiple installations, one after other and unbilled time in b/w

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources