CODEWITHSUNDEEP
sqloracle-database
user1726550
user1726550

Reputation: 81

SQL query to retrieve results based on date and time

I have the following tables with data:

customer: 

cus_id             cus_name
1                  philip
2                  david
3                  morris

order: 

 order_id   order_no    cus_id  ordr_status      order_ date_time
    1             123        1       d                      20121015 13:10:01
    2             456        1       c                      20121014 14:20:00
    3             789        2       d                      20121013 17:10:01
    4             767        2       c                      20121014 15:10:00

Based on this data, I want to retrieve both customer and order information if the order date time is between 20121014 14:00:00 and 20121015 14:00:00 and if the the order status is (d,c) and they have same oder id's.

Upvotes: 1

Views: 11114

Answers (3)

Ramu Nayakudu
Ramu Nayakudu

Reputation: 1

Please use below query in Oracle DB:

select * from table_name where to_char(date_field,'YYYYMMDD HH24MISS') > '20190410 180614'

The above query fetches the records from a table, which were inserted into the table after 10-apr-2019 18:06:14 hrs.

Upvotes: 0

Marlin Pierce
Marlin Pierce

Reputation: 10079

Well, for orders,

SELECT * 
FROM order
WHERE NOT order_date_time between '2012-10-14 14:00:00' and '2012-10-15 14:00:00'

Joining to customers is left as an exercise for the reader.

Upvotes: 1

Justin
Justin

Reputation: 9724

If I understand you want this:

QUERY SQLFiddle example:

SELECT
c.cus_name
,o.order_no
,o.ordr_status
,o.[order_ date_time]
FROM
customer c
  RIGHT JOIN order1 o
  ON c.cus_id = o.cus_id
WHERE o.[order_ date_time] >='20121014 14:00:00'
  AND o.[order_ date_time] <='20121015 14:00:00'
  AND (o.ordr_status = 'd' OR o.ordr_status = 'c')

Result:

| CUS_NAME | ORDER_NO | ORDR_STATUS |  ORDER_ DATE_TIME |
---------------------------------------------------------
|   philip |      123 |           d | 20121015 13:10:01 |
|   philip |      456 |           c | 20121014 14:20:00 |
|    david |      767 |           c | 20121014 15:10:00 |

Upvotes: 2

Related Questions