Time Between SQL Query

Goran Bibic

6y1.2kSQL

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankajkumar Patel6y ago
Accepted answer
Hi Goran Bibic,

You can cast "vrijemeprijave" as time and check it with Between as like following:
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) AND CAST(VRIJEMEPRIJAVE AS TIME) BETWEEN '05:59:59.0000000' and '21:59:59.0000000' 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) AND CAST(VRIJEMEPRIJAVE AS TIME) BETWEEN '05:59:59.0000000' and '21:59:59.0000000' 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
Hope, this will help you!
Pankajkumar Patel6y ago
Hi Goran Bibic,

Can you please share the sample data for which you are expecting the given result.

Because, it is very difficult to know exact above given result without exact data.
Goran Bibic6y ago
Pankaj Patel

When I put this code no have errors, but result is empty.
If worker cheked from 09-17h
Need to be result if we put time beetwen
BETWEEN '05:59:59.0000000' and '21:59:59.0000000'

Result is empty

Maybe becose of time 19 h must be other value 19:00 or somesthing?

Time Between SQL Query

Pankaj Patel

BETWEEN '05:59:59.0000000' and '21:59:59.0000000'