How to format date in where?

Question

I have this script:

select * from OPDN A 
where A."DocDate" between '2020/01/01' and '2020/01/31'

How to achieve the format "MM/DD/YYYY" in the WHERE clause?

select * from OPDN A 
where A."DocDate" between '01/01/2020' and '01/31/2020'

The column DocDate is of type TimeStamp.

Answer 1

If the column data type is TIMESTAMP as the OP has written in a comment to Sandra Rossi's excellent answer then there are actually three conversions required to make the selection work as expected.

1. turn the first selection parameter (between a ...) into a date
2. turn the second selection parameter (between ... and b) into a date
3. turn the timestamp column DocDate into a date

This looks like this:

select 
        * 
from 
     OPDN A 
where 
    to_date(A."DocDate") between to_date('01/01/2020', 'MM/DD/YYYY')
                             and to_date('01/31/2020', 'MM/DD/YYYY');

The conversion of the selection parameters with the help of format-strings should be obvious, and the result is the same information in a SQL date data type.
The third conversion (to_date(a."DocDate")) might be surprising, but is rather important.

The way the selection is meant to work is to include everything from the very start of the selection period to the very end. If the input filters are simply converted to timestamps then they will have the time-component of 00:00 (midnight), as no specifics about the time of day are provided.
This will lead to all "DocDate" values after midnight to be excluded.

The correct level of comparison here is date, therefore the "DocDate" also needs to be converted to the hour-less date data type.

p.s. I just realized that the selection criteria are given in MM/DD/YYYY format which is probably the reason for the conversion error the OP received earlier.

Answer 2

It depends what is the type and format of the column DocDate.

1. In SAP ERP softwares (R/3, ECC, S/4HANA), the dates were historically defined as NVARCHAR types of 8 characters, with the format 'YYYYMMDD'.

2. In SAP Business One, I don't know...

3. In SAP HANA database, there are also four specific types (SAP Library "Datetime Data Types"): DATE, TIME, SECONDDATE, TIMESTAMP.

Solutions for each case:

1. If DocDate type is NVARCHAR 8 with format YYYYMMDD: select * from OPDN A where A."DocDate" between '20200101' and '20200131'

2. In SAP Business One, I don't know...

3. If DocDate type is DATE: select * from OPDN A where A."DocDate" between to_date('01/01/2020','DD/MM/YYY') and to_date('01/31/2020', 'DD/MM/YYY')

Answer 3

In Oracle, it can do that

select * from OPDN A 
where to_date(A."DocDate", 'DD/MM/YYY') between to_date('01/01/2020','DD/MM/YYY') and to_date('01/31/2020', 'DD/MM/YYY');