Date range comparison with hours, minutes and seconds [message #690136] |
Wed, 23 October 2024 12:17 |
|
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 #690138 is a reply to message #690136] |
Wed, 23 October 2024 13:42 |
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 thisto_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 conversions.
|
|
|
|