Home » SQL & PL/SQL » SQL & PL/SQL » Date range comparison with hours, minutes and seconds (Oracle 19c)
Date range comparison with hours, minutes and seconds [message #690136] Wed, 23 October 2024 12:17 Go to next message
oracle12c_user
Messages: 2
Registered: February 2019
Junior Member
Hi Experts,

Kindly help me with the below requirement.

i have a table with sales data as contains below

Sales_Data
====================
column_id    Sales_Date
---------    -----------------------
1            01-JUL-24 03:48:27 PM
2            29-AUG-24 01:20:46 PM
3            09-SEP-24 10:42:54 AM


User Input
=========================
Input Date From :  01-JUL-2024 , Input Time From : 01:00:00 AM
Input Date To   :  01-JUL-2024 , Input Time To   : 16:30:45 PM

it should compare the date and time also and display the data accordingly.
for example it should compare the date between input date from to input date to including the time
column id 1 has sales date as 01-Jul024 03:48:27 PM which in between input parameters 01-JUL-2024 01:00:00 AM to 24-DEC-2024 16:30:45
so it should display the column id 1 record as it is in between the time 01:00:00 and 16:30:45.

i have tried the below query but return no data

SELECT Column_id,
      Sales_Date
FROM   Sales_Data
WHERE to_date(Sales_Date,'DD-MON-YYYY HH24:MI:SS')
BETWEEN TO_DATE(to_char(to_date(Input_Date_From,'DD-MON-YYYY'),'DD-MON-YYYY')||' '||Input_Time_From,'dd-mon-yyyy hh24:mi:ss') AND TO_DATE(to_char(to_date(Input_Date_To,'DD-MON-YYYY'),'DD-MON-YYYY')||' '||Input_Time_To,'dd-mon-yyyy hh24:mi:ss')

kindly help me with this requirement.

thanks in advance.

please let me know if i missed anything here.


data:
=======
Select 1 Column_id, to_date('01-JUL-24 03:48:27', 'DD-MON-YYYY hh24:mi:ss') Sales_Date From Dual
Union all
Select 2 Column_id, to_date('29-AUG-24 01:20:46', 'DD-MON-YYYY hh24:mi:ss') Sales_Date From Dual
Union all
Select 3 Column_id, to_date('09-SEP-24 10:42:54', 'DD-MON-YYYY hh24:mi:ss') Sales_Date From Dual;
Re: Date range comparison with hours, minutes and seconds [message #690137 is a reply to message #690136] Wed, 23 October 2024 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68709
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Just (assuming Sales_Date is of DATE datatype):
select * from Sales_Data 
where Sales_Date between to_date('&Input_Date_From &Input_Time_From','<your data datetime format')
                     and to_date('&Input_DateTo &Input_Time_To','<your data datetime format')
/
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your tables and data. Explain with words and sentences the rules that lead to this result.

Re: Date range comparison with hours, minutes and seconds [message #690138 is a reply to message #690136] Wed, 23 October 2024 13:42 Go to previous messageGo to next message
John Watson
Messages: 8958
Registered: January 2010
Location: Global Village
Senior Member
More than likely your problem is because you are mis-using data types. SALES_DATE is a DATE type, so this
to_date(Sales_Date,'DD-MON-YYYY HH24:MI:SS')
is impossible because TO_DATE takes a string as an argument, not a date. Uncle Oracle tries to be helpful: it implicitly type casts SALES_DATE to a string, so that the TO_DATE can succeed. That implicit type casting is done using whatever NLS settings happen to be in effect for your session, which are almost certainly not 'DD-MON-YYYY HH24:MI:SS'. You need to remove all those
TO_DATE(to_char(to_date(
conversions.

Re: Date range comparison with hours, minutes and seconds [message #690147 is a reply to message #690138] Fri, 01 November 2024 01:11 Go to previous message
oracle12c_user
Messages: 2
Registered: February 2019
Junior Member
Thanks Michel and John. I understood the issue and it is resolved now.
Previous Topic: Problem with passing values to Function
Next Topic: SQL count with comma delimit
Goto Forum:
  


Current Time: Fri Nov 08 19:23:17 CST 2024