CODEWITHSUNDEEP
sqloracle-databasedatetime
tia97
tia97

Reputation: 350

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

I've seen a few answers to questions similar to mine but I cannot get them to work. I have several date fields in my query that return the date and time like such 7/1/2014 12:00:00 AM. Is there a way I can just have the fields show 7/1/2014?

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

I basically would like to cut down the two date fields to the shorter date format minus the time.

Thanks in advance!

Upvotes: 11

Views: 127973

Answers (7)

Patra Adhar
Patra Adhar

Reputation: 1

UPDATE your_table SET created = TRUNC(modified);

Upvotes: 0

Erhan Mutlu
Erhan Mutlu

Reputation: 11

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

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

DEPENDS on the data type.

If the column is DATE data type, then, as suggested already, TRUNC would do the job to display. But, if your locale-specific NLS date settings are different, then you will still see the time portion as midnight.

Else, you need to use TO_DATE with proper FORMAT and apply TRUNC to it.

update

If you only want to display, use TO_CHAR, else, if you have a filter in your WHERE clause, then remember TO_CHAR doesn't return DATE, it converts it into literal.

Upvotes: 6

Bacs
Bacs

Reputation: 919

The Oracle date datatype always includes the time.

TRUNC will truncate the time to midnight, which you will need to do if you want to match the date parts of two datetimes. The time may still display, depending on how your client is configured, so use TO_CHAR with an appropriate format mask to display it whatever way you want.

Upvotes: 1

neshkeev
neshkeev

Reputation: 6476

Try this:

SQL> select to_char(sysdate, 'YYYY/MM/DD') dateonly, sysdate datetime from dual;

DATEONLY   DATETIME
---------- -------------------
2014/09/26 2014-09-26 15:41:03

Upvotes: 2

Aramillo
Aramillo

Reputation: 3216

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.....

Upvotes: 12

Vulcronos
Vulcronos

Reputation: 3456

Just use the function TRUNC.

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

Upvotes: 15

Related Questions