OraFAQ Forum: SQL & PL/SQL » Date range comparison with hours, minutes and seconds

Home » SQL & PL/SQL » SQL & PL/SQL » Date range comparison with hours, minutes and seconds (Oracle 19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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;

Report message to a moderator

Re: Date range comparison with hours, minutes and seconds [message #690137 is a reply to message #690136]

Wed, 23 October 2024 13:37

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just (assuming Sales_Date is of DATE datatype):

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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: 8974
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

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

TO_DATE(to_char(to_date(

conversions.

Report message to a moderator

Re: Date range comparison with hours, minutes and seconds [message #690147 is a reply to message #690138]

Fri, 01 November 2024 01:11

oracle12c_user
Messages: 2
Registered: February 2019

Junior Member

Thanks Michel and John. I understood the issue and it is resolved now.

Report message to a moderator

Previous Topic:	Problem with passing values to Function
Next Topic:	SQL count with comma delimit

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 01:55:15 CDT 2025