TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Goran Bibic
480
2.9k
198.3k
Time Between SQL Query
Jan 20 2020 3:24 PM
Need to put in my code time beetwen 05:59:59 to 21:59:59
In line 4 and 5
Datetime format columns
vrijemeprijave and
vrijemeodjave
are 2019-12-31 09:38:23.917
SELECT radnik as
'Radnik'
,
SUM(CASE WHEN DATEPART(WEEKDAY,vrijemeprijave) in (5, 6) then 0
else
DATEPART(HOUR,vrijemeprijave)END) as
'Prijava'
,
SUM(CASE WHEN DATEPART(WEEKDAY,vrijemeodjave) in (5, 6) then 0
else
DATEPART(HOUR,vrijemeodjave)END) as
'Odjava'
,
ISNULL(SUM(CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) then 0
else
(CASE WHEN (DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) END)END),0) AS
'Radni sati'
,
ISNULL(SUM(CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) then 0
else
(CASE WHEN (DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) END)END),0) AS
'Nocni'
FROM dbo.prijava_radnika
where radnik=
'Bibic Goran'
AND day(vrijemePrijave) = 24 and MONTH(vrijemePrijave) =
'12'
AND YEAR(vrijemePrijave) =
'2019'
GROUP BY radnik, IdPrijava, vrijemeprijave, vrijemeodjave
Result now is :
Radnik Prijava Odjava Radni sati Nocni
Bibic Goran 22 6 8 8
Need to be with new code:
Radnik Prijava Odjava Radni sati Nocni
Bibic Goran 22 6 0 8
Reply
Answers (
3
)
How to convert values String to numeric?
How to Merge Date and Time Field?