0

I am trying to get records from 29th April,2022 on hourly basis from Oracle DB, however with the below query I am getting records count older than 29th April as well(all previous records count as well). Can you help fine tune the query?

SELECT DISTINCT
    COUNT(*),
    STATUS,
    TO_CHAR(LOAD_DATE,'DD-MON-YY HH24')
FROM
    TARGET_HIST
WHERE
    STATUS = 'A'
    AND TO_CHAR(LOAD_DATE, 'DD-MON-YY HH24:MI:SS') > '29-APR-22 00:00:00'
GROUP BY
    STATUS,
    TO_CHAR(LOAD_DATE,'DD-MON-YY HH24')
ORDER BY
    STATUS,
    TO_CHAR(LOAD_DATE,'DD-MON-YY HH24');
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Ashok
  • 43
  • 6
  • Comparing dates with less or greater than as strings will never work very well unless you use ISO format. For example, the string '29-AUG-22 00:00:00' will be less than '29-JUL-22 00:00:00' which makes sense as a string but not as a date. – Joachim Isaksson May 02 '22 at 05:28
  • If `TO_CHAR` converts the date to a VARCHAR (string), wouldn't your comparison end up being between two strings (e.g. "string1" > "string2") and not between two dates? Shouldn't you convert `'29-APR-22 00:00:00'` to a date and then compare it? – Cully May 02 '22 at 05:29
  • Does this answer your question? [Comparing Dates in Oracle SQL](https://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql) – Cully May 02 '22 at 05:31
  • THE LOAD_DATE is a column name here . i am new to sql can you help to edit the query in the right way to retrieve the records count , i need three coulmns to display count, status, date(hourly from 20-apr-22, 00:00:00 – Ashok May 02 '22 at 05:35
  • @Ashok If you have more details to add to your question, edit the question itself and add them there. – Cully May 02 '22 at 05:49
  • There are also two suggestions in the comments on how to fix the issue you describe in your question. Did that fix it? – Cully May 02 '22 at 05:50
  • Hi @Cully, i have edited the question after modifying the query as per your suggestion, i am able to get records with dates 29th and above till sysdate but not in order as mentioned in question – Ashok May 02 '22 at 06:06
  • 1
    @Ashok That's basically another question than your original question. You might want to post a new question for it. – Cully May 02 '22 at 06:12
  • I've rolled back the changes made to the request, so the answers match the question being asked. – Thorsten Kettner May 02 '22 at 06:43

2 Answers2

0

Try this. Since you didn't provide any sample data I didn't test it for you


select trunc(load_date,'HH') "HOUR", count(*)
  from target_hist
where status='A' AND
load_date between to_date('29/04/2022','DD/MM/YYYY') and to_date('29/04/2022 23:59:59','DD/MM/YYYY HH24:MI:SS');
Group by trunc(load_date,'HH')
Order by trunc(load_date,'HH')
Beefstu
  • 804
  • 6
  • 11
  • Truncating the datetime to hours is the correct solution, but your where clause may dismiss the day's last second, in case it is stored as a timestamp, e.g. '2022-04-29 23:59:59.001'. If you want to restrict this to April 29, then use `>=` and `<`. And use date literals instead of converting strings. `load_date >= date '2022-04-29' and load_date < date '2022-04-30'`. – Thorsten Kettner May 02 '22 at 06:39
  • @Thorsten thanks I didn't think about that scenario – Beefstu May 02 '22 at 08:13
0

Two problem with the same reason:

  • In your WHERE clause you look for rows after '29-APR-22 00:00:00', but you get rows before that.
  • In your ORDER BY clause you get the dates sorted in a mangled order.

This is because you have converted the datetimes to strings where '29-APR-22' comes after '01-MAY-22', but before '30-JAN-22', because '2' comes after '1' and before '3'.

If you want to sort and compare datetimes, then use datetimes. You can truncate them down to the hour with TRUNC(load_date, 'hh').

select
  trunc(load_date, 'hh') as load_hour,
  status,
  count(*)
from target_hist
where status = 'A'
and load_date >= date '2022-04-29'
group by trunc(load_date, 'hh'), status
order by trunc(load_date, 'hh'), status;

Leave it to your app to display the datetime in the format the user wants to see it. If you need a particular format, e.g. for exporting the data into a file, you can apply TO_CHAR on the truncated datetime TO_CHAR(trunc(load_date, 'hh'),'DD-MON-YY HH24') in the select clause (and only there).

Please note that I have removed DISTINCT from the query, because there are no duplicates to remove. And I am using a date literal in the WHERE clause. And >=in order to include midnight.

This query considers all days since April 29. If you want this day only, then add and load_date < date '2022-04-30'.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • I need to display load_date as below order 29-APR-22 00, 29-APR-22 01, 29-APR-22 02, 30-APR-22 01 – Ashok May 02 '22 at 06:30
  • Yes, I know. The query does that. What makes you think it doesn't? – Thorsten Kettner May 02 '22 at 06:32
  • yes it works but it displays only date as 29-apr-2022 not the time in hr. I need to get output as 29-APR-22 00, 29-APR-22 01 – Ashok May 02 '22 at 07:06
  • Then your app or tool is supressing the hour. If it's a tool look for the appropriate settings. If it's an app you have written, it's probably either your code or a system setting. – Thorsten Kettner May 02 '22 at 07:42