Oracle SQL "Select Date from Datetime Field "

Oracle SQL SELECT DATE from DATETIME field

TO_DATE (REPORTDATE, 'DD.MON.YYYY')

This makes no sense. You are converting a date into a date again. You use TO_DATE to convert a string literal into DATE.

I want result to return only 29.10.2013

You could use TRUNC to truncate the time element. If you want to use this value for DATE calculations, you could use it directly.

For example,

SQL> select TRUNC(SYSDATE) dt FROM DUAL;

DT
---------
12-MAR-15

To display in a particular format, you could use TO_CHAR and proper FORMAT MASK.

SQL> SELECT to_char(SYSDATE, 'DD.MM.YYYY') dt from dual;

DT
----------
12.03.2015

SQL>

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')

Convert datetime field to just a date field in SQL (Oracle)

Use to_char function:

SELECT DISTINCT
to_char(C.RECEIPTDATE,'DD/MM/YYYY'),
(I.CLIENTID ||' - '||PO.CLIENTNAME) AS CLIENT,
D.INVOICEID,
D.SVCFROMDATE,
D.SVCTODATE,
D.SVCCODE
FROM M_EQP_ORDERS
WHERE.....

Querying datetime in oracle

The problem occurs because of

to_date(end_time,'dd/mon/yyyy hh24:mi:ss')

This is a wrong usage of the to_date function. To_date converts a string to a date.
When Oracle sees this expression, it will automatically convert the end_time value to a string, using the configured date format of your database/session. This format typically doesn't include the time part, so a date with the value of "27/10/2013 8:00:00 AM" will be converted to the string "27/10/2013" (if your database date format is dd/mm/yyyy).
Your to_date expression will then convert the string value "27/10/2013" back to a date. The resulting date value will be "27/10/2013 00:00:00", so you will have lost the time portion of your original date.

The simple and correct solution is to drop the to_date(end_time) expression and just use end_time. This will also ensure that if you have index on end_time, the query will be able to use that index.

select * 
from conference_hall_book 
where end_time <= to_date('26/oct/2013 15:00:00','dd/mon/yyyy hh24:mi:ss')

Reading date only with PL/SQL from datetime field , from informix database

You will probably find that the database expected the date to be entered using the format dd/mm/yyyy but whatever application deposited used the format dd/mm/yy and trying to format it using dd/mm/yyyy the database just accepts it as being in the 1st century (rather than as expected the 21st century).

For example:

SELECT TO_DATE( '30/06/14', 'dd/mm/yy' )   AS date1,
       TO_DATE( '30/06/14', 'dd/mm/yyyy' ) AS date2
FROM   DUAL;

Outputs:

DATE1               DATE2
------------------- -------------------
2014-06-30 00:00:00 0014-06-30 00:00:00

You could try to correct it using something like this:

SELECT CASE
       WHEN TO_DATE( "last_updated", 'dd/mm/yyyy' )
              BETWEEN DATE '0000-01-01' AND DATE 0100-01-01'
       THEN TO_DATE( "last_updated", 'dd/mm/yyyy' ) + INTERVAL '2000' YEAR(4)
       ELSE TO_DATE( "last_updated", 'dd/mm/yyyy' )
       END
FROM   "smetka"@informix

or

SELECT CASE
       WHEN REGEXP_LIKE( "last_updated", '\d{1,2}/\d{1,2}/\d{1,2}' )
       THEN TO_DATE( "last_updated", 'dd/mm/yy' )
       ELSE TO_DATE( "last_updated", 'dd/mm/yyyy' )
       END
FROM   "smetka"@informix

SQL How do I extract time from a date to HH:MI:SS PM/AM?

You can use TO_CHAR( datetime, format ) to format a DATE column. The format options are given here.

This would give you the time part of a date value (12-hour clock plus AM/PM):

SELECT TO_CHAR( column_name,'HH:MI:SS AM') 
FROM   your_table

Edit - Addressing the update

You can do:

SELECT CASE WHEN TO_NUMBER( TO_CHAR( a.dtime_appl_creation, 'HH24' ) ) >= 12
            THEN TO_CHAR( a.dtime, 'HH:MI:SS PM' )
            END AS "date"
FROM   table_name

---

Related Topics