Select from Table by Knowing Only Date Without Time (Oracle)

Select from table by knowing only date without time (ORACLE)

DATE is a reserved keyword in Oracle, so I'm using column-name your_date instead.

If you have an index on your_date, I would use

WHERE your_date >= TO_DATE('2010-08-03', 'YYYY-MM-DD')
AND your_date < TO_DATE('2010-08-04', 'YYYY-MM-DD')

or BETWEEN:

WHERE your_date BETWEEN TO_DATE('2010-08-03', 'YYYY-MM-DD')
AND TO_DATE('2010-08-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

If there is no index or if there are not too many records

WHERE TRUNC(your_date) = TO_DATE('2010-08-03', 'YYYY-MM-DD')

should be sufficient. TRUNC without parameter removes hours, minutes and seconds from a DATE.


If performance really matters, consider putting a Function Based Index on that column:

CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date));

Get only date without time in Oracle

Usually one would simply truncate the datetime with TRUNC:

TRUNC(pa.fromdate)

This removes the time part from the datetime, so you get the mere date. Then in your application layer, you would care about how to display it.

For example you have a GUI with a grid. The grid displays the dates according to the user's system settings (e.g. Windows region settings), but the grid knows it's dates and can sort accordingly. For this to happen, you'd fill the grid with dates, not with strings representing a date.

If you want a fixed string format (e.g. in order to write into a file), you can use TO_CHAR instead:

TO_CHAR(pa.fromdate, 'dd.mm.yyyy')

Oracle select date without time and keep date as data type

In Oracle there is no date data type that has only a year-month-day component.

The DATE data type is stored internally as 7- or 8-bytes which always has year (2-bytes), month (1-byte), day (1-byte), hour (1-byte), minute (1-byte) and second (1-byte).

The TIMESTAMP data type also has fractional seconds (and can also have a time zone).

Can I try something else?

No, you either use a VARCHAR2 string or use a DATE or TIMESTAMP and accept that it has a time component.

select sysdate from dual returns only date, not time

Change your NLS settings: Tools -> Preferences -> Database -> NLS

Changing Default Date Format settings

oracle SQL how to remove time from date

When you convert your string to a date you need to match the date mask to the format in the string. This includes a time element, which you need to remove with truncation:

select 
p1.PA_VALUE as StartDate,
p2.PA_VALUE as EndDate
from WP_Work p
LEFT JOIN PARAMETER p1 on p1.WP_ID=p.WP_ID AND p1.NAME = 'StartDate'
LEFT JOIN PARAMETER p2 on p2.WP_ID=p.WP_ID AND p2.NAME = 'Date_To'
WHERE p.TYPE = 'EventManagement2'
AND trunc(TO_DATE(p1.PA_VALUE, 'DD-MM-YYYY HH24:MI')) >= TO_DATE('25/10/2012', 'DD/MM/YYYY')
AND trunc(TO_DATE(p2.PA_VALUE, 'DD-MM-YYYY HH24:MI')) <= TO_DATE('26/10/2012', 'DD/MM/YYYY')

Outside the scope of the question, but storing dates as strings is bad practice, and storing date times is even worse.

  1. We need to convert the strings to dates in order to do any form of date processing (arithmetic, interval assessment, etc) on them
  2. Strings offer no guarantees regarding format, so we run the risk of date corruption crashing our code. We can defend against this by employing VALIDATE_CONVERSION() (available since 12c, find out more ) but it's still a PITN
  3. Using non-standard datatypes makes it harder to reason about the data model and the code we build over it.

Select date time with condition (Oracle)

My comment "upgraded" to a proper answer upon request:

Your error message shows you are using: [AR System ODBC Driver].

You are using an ODBC datasource that connects to your AR System and not directly to the Oracle database. Which would mean that the syntax of the query would have to be whichever syntax AR System allows (even though Oracle database runs "underneath" AR System.) Therefore you get error using TO_DATE.

You either need to figure out proper syntax for date queries in AR System.

Or you can switch to an Oracle ODBC driver and datasource, but then you probably need different connection credentials as you would be bypassing your AR System and going to the database directly. That may be a security issue in your setup.

I think you should ask someone with AR System knowledge, not so much Oracle knowledge.

EDIT:

Google for "AR System" "Oracle" gives this reference as first hit:

http://www.unc.edu/remedy/clients/7.0.1/BMC%20Remedy%20AR%20System%20Server%207-0-01/Database-Ref-700.pdf

That reference manual shows how the AR System can run on several different relational databases, Oracle being one of them. Page 23 shows that an AR System "timestamp" is saved in Oracle as a number. I am almost sure that this means that when you use the AR System ODBC driver, you need to write the SQL in the ODBC call in AR System syntax. Then the AR System ODBC driver rewrites this to the syntax needed in whatever relational database the AR System is installed upon.

So it reinforces my suggestion to ask AR System developers rather than Oracle developers.

Check if current date is between two dates Oracle SQL

You don't need to apply to_date() to sysdate. It is already there:

select 1
from dual
WHERE sysdate BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND TO_DATE('20/06/2014', 'DD/MM/YYYY');

If you are concerned about the time component on the date, then use trunc():

select 1
from dual
WHERE trunc(sysdate) BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND
TO_DATE('20/06/2014', 'DD/MM/YYYY');

How can a query select dates from only a specific academic year?

This is an Oracle equivalent of Andomar's post -

select
*
from
dts
where
case
when extract (month from dt) < 9 then extract (year from dt) - 1
else extract (year from dt)
end = extract (year from sysdate)

Fiddle: http://sqlfiddle.com/#!4/75a16/1/0



Related Topics



Leave a reply



Submit