Home » SQL & PL/SQL » SQL & PL/SQL » date usage in SQL query
date usage in SQL query [message #677498] Mon, 23 September 2019 17:31 Go to next message
James1116
Messages: 4
Registered: September 2019
Junior Member
HI,

can anyone tell me why following query not return any result?

select * from XXYH.XXYH_MTL_ITEMS_INTF_STG where creation_date between to_date('20-sep-2019 00:00:00', 'dd-mon-yyyy hh24:mi:ss')
and to_date('24-sep-2002 23:59:59', 'dd-mon-yyyy hh24:mi:ss')


my creation_date format is like '23-SEP-10'

Thanks
James
Re: date usage in SQL query [message #677499 is a reply to message #677498] Mon, 23 September 2019 18:16 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
James1116 wrote on Mon, 23 September 2019 15:31
HI,

can anyone tell me why following query not return any result?

select * from XXYH.XXYH_MTL_ITEMS_INTF_STG where creation_date between to_date('20-sep-2019 00:00:00', 'dd-mon-yyyy hh24:mi:ss')
and to_date('24-sep-2002 23:59:59', 'dd-mon-yyyy hh24:mi:ss')


my creation_date format is like '23-SEP-10'

Thanks
James

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


the correct syntax for the BETWEEN clause is BETWEEN {FROM_DATE} AND {TO_DATE}; so you need to reverse the two values

between to_date('24-sep-2002 23:59:59', 'dd-mon-yyyy hh24:mi:ss')
AND
to_date('20-sep-2019 00:00:00', 'dd-mon-yyyy hh24:mi:ss')
Re: date usage in SQL query [message #677500 is a reply to message #677499] Mon, 23 September 2019 18:22 Go to previous messageGo to next message
James1116
Messages: 4
Registered: September 2019
Junior Member
Thanks, it does work after I reverse the date.

The following query is to look for duplicate description.

select item_description, count(*) from XXYH.XXYH_MTL_ITEMS_INTF_STG
group by item_description having count ( item_description ) > 1

How do I modify this query by adding "where" to order by creation date desc, also I would like to list 2 creation date if there are 2 duplicate values.
and list 3 creation dates if there are 3 duplicate values.

Many Thanks
James
Re: date usage in SQL query [message #677501 is a reply to message #677500] Mon, 23 September 2019 19:14 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Consider to actually READ & follow Posting Guidelines

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: date usage in SQL query [message #677505 is a reply to message #677498] Tue, 24 September 2019 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the result of:
descXXYH.XXYH_MTL_ITEMS_INTF_STG 
But before, Please read How to use [code] tags and make your code easier to read.

Re: date usage in SQL query [message #677520 is a reply to message #677500] Tue, 24 September 2019 06:40 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
In addition to reading the guidelines, you should open a new thread when you have a different, unrelated question. A single thread should focus on a single problem.
Re: date usage in SQL query [message #677601 is a reply to message #677520] Mon, 30 September 2019 05:46 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
SELECT A.Item_description, A.Creation_date
    FROM Xxyh.Xxyh_mtl_items_intf_stg A,
         (  SELECT Item_description
              FROM Xxyh.Xxyh_mtl_items_intf_stg
          GROUP BY Item_description
            HAVING COUNT (Item_description) > 1) B
   WHERE A.Item_description = B.Item_description
ORDER BY A.Item_description, A.Creation_date
Previous Topic: Need help with Error: subprogram or cursor reference is out of scope
Next Topic: Cannot drop a user
Goto Forum:
  


Current Time: Mon Sep 21 02:37:50 CDT 2020