I'm completely new to SQL and I'm struggling. I need to run a query from the dates mentioned below and split the timestamped data into shift hours 6-13:59, 14-21:59 and 22 - 05:59. I need to see how many shipments have been sent in between those hours. I came up with something like this, but it doesn't seem to work.

When I put the date without the timestamp the data gets returned, but I want to have only those rows returned that have been sent in between 6 - 14. The SDG table includes a timestamp, so I don't understand why it doesn't work.

select s1.date, k.su, count(distinct s1.nr) AMO_SHIP2021
from sdg s1 , client k
where s1.clientnr in ('1')
and s1.date between '01.12.2021 06:00:00' and '14.12.2021 13:59:59'
and s1.function in ('20')
and k.nr = s1.clientnr
group by s1.date, k.su, s1.function

CodePudding user response：

You should post the table structure there. Generally, if your column is of Date type you need to operate dates using BETWEEN with dates and not strings

s1.date BETWEEN to_date('01.12.2021 06:00:00', 'DD.MM.YYYY HH24:MI:SS') AND
to_date('14.12.2021 13:59:59', 'DD.MM.YYYY HH24:MI:SS')

Refer in your search to FORMAT MODEL.

to_date('string date', 'format model')