For a date field 15:00:00 (2016-11-11), query the field is greater than is greater than a certain range interval, how do you usually check?
Note: if above dates for the day, hope to investigate and punish the data of the day

I wrote three ways, feels not so good... Haha

Methods a
AND CM. DELIVERY_DATE & gt;=trunc (to_date (' 20140101 ', 'yyyymmdd'))
AND trunc (CM) DELIVERY_DATE) & lt;=to_date (' 20141230 ', 'yyyymmdd')

The second way:
AND to_char (CM) DELIVERY_DATE, 'yyyyMMdd') & gt;
='20141219'AND to_char (CM) DELIVERY_DATE, 'yyyyMMdd') & lt;
='20141219'
Three:
AND CM. DELIVERY_DATE & gt;=to_date (' 20141219 '| |' 00:00:00 ', 'yyyymmdd hh24: mi: ss')
AND trunc (CM) DELIVERY_DATE) & lt;=to_date (' 20141219 '| |' 23:59:59 ', 'yyyymmdd hh24: mi: ss')

Do you usually use a kind, or other means

CodePudding user response:

AND CM. DELIVERY_DATE & gt;=trunc (to_date (' 20140101 ', 'yyyymmdd'))
AND CM. DELIVERY_DATE & lt; To_date (' 20141230 ', 'yyyymmdd') + 1

CodePudding user response:

1, one day
Trunc (CM. DELIVERY_DATE)='01 - jan - 14'
During the period of 2,
Trunc (CM) DELIVERY_DATE) between '01 - jan - 14' and '31 dec - 14'

Free play.

CodePudding user response:

Avoid calculation on the column, such as trunc, to_char these functions;

CodePudding user response:

Can use the way to the first floor, so easy to use index

You listed three ways, it is easy to cause the index cannot be used