Oracle

Oracle - Dates

Notice the date usage here

1
2
3
4
5
6
7
8
9
10
11
select PO.PONUM, DESCRIPTION, ORDERDATE, STATUS, PO1, CNT
from MAXIMO.PO 
left join (
        select PONUM, count(*) CNT
        from    MAXIMO.POLINE
        group by PONUM
        ) B on PO.PONUM = B.PONUM
-- where PO.ponum='35855';
where PO.ORDERDATE >= DATE '2021-01-01'
and b.CNT is null
order by PO.PONUM;

Note: In this example - Collect_Date had a datatype of DATE

1
2
3
4
5
select collect_date, collect_name, submitter_seq, expected_date
from sample_schedules_events
where to_date(collect_date) = to_date('29-DEC-24', 'DD-MON-YY')
and submitter_seq = 11411
order by collect_date

There might be numbers in the time portion of the Collect_Date column (which there shouldn’t, because this is declared as a date). When I wrap ‘to_date’ around collect_date I started getting results

A second way to fix the collect_date function is to use the trunc function

1
where trunc(collect_date) = to_date('29-DEC-24', 'DD-MON-YY')

which truncates off the time.

Here is how to turn a varchar into a timestamp

1
2
3
4
select to_timestamp('2022-07-12 07:35:00 AM', 'YYYY-MM-DD HH:MI:SS AM') dt, d.*
from limsQAM.wims_data d
where location='SSB I-8 BAC' 
and sampnum='2207120091' and  final=500

more information: