How to remove timestamp from date in cmn_audits

[sparc.helpdesk]

The cmn_audits table contain column which has value_before column declared as varchar. While auditing for Date of Hire of resources we need to only see the date and not the timestamp.

So how can we remove the time data from date of value_before column?

[Suman Pramanik]

Typecast use the cast function http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj33562.html

[L.Elias]

For MSSQL

[Nick Darlington]

For Oracle the common solution for dropping the time portion of a date is to use trunc() though sometimes this will still leave the time portions visible but with just 00:00:00.

 

The article linked two replies up isn't for the Oracle database, but for Java DB (similar to Apache's Derby DB).

 

See some of the differences here:

 

select cast(sysdate as date), trunc(sysdate), sysdate from dual

 

CAST(SYSDATEASDATE)     TRUNC(SYSDATE)       SYSDATE           
----------------------  -------------------  -------------------
02/09/2015 11:22:06     02/09/2015 00:00:00  02/09/2015 11:22:06

 

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 1/ms]

[Executed: 02/09/15 13:21:16 CDT ] [Execution: 133/ms]

 

Of course, the issue is that changing the data type doesn't necessarily change how it is displayed - this is a client-side configuration (where the 'client' may be a query tool, a report designer tool, or a portlet in Clarity), for example the same issued using SQL*Plus on my environment:

 

SQL> select cast(sysdate as date), trunc(sysdate), sysdate from dual
  2  ;

CAST(SYSDATEASD TRUNC(SYSDATE)  SYSDATE
--------------- --------------- ---------------
02-SEP-15       02-SEP-15       02-SEP-15

SQL>

 

Now, with MSSQL, the same conditions apply - you can change the data type by casting (amongst other techniques), but it is still down to the client as to how this will ultimately be displayed:

 

select convert(date, getdate()), getdate()

 

column1     column2                
----------  -----------------------
2015-09-02  2015-09-02 14:50:32.767

 

1 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 0/ms]

[Executed: 02/09/15 13:28:45 CDT ] [Execution: 62/ms]

 

Here it did what you wanted, but that isn't to say a different client or environment will yield the same result.

 

That is why in my earlier reply I said that the answer is going to depend on what tools / apps are being used to display the output, as that control typically belongs at a level higher than the SQL and DBMS being issued (with the one exception of force converting the date fields into strings, but that then often loses localization as well).

[Nick Darlington]

This will depend on what you are using to view the data.  If it's the out-of-the-box audit trail pages, then the information will be as you see it including the time portion of the fields - you will not be able to make a change to that directly, and I would suggest raising an Idea if you wish to have the data displayed differently according to attribute configurations.

 

If you are using your own portlet or report on the data, then control over the formatting will down to the SQL and data types you use and the answer will depend on exactly which approach you have been taking (you would need to share more details about your setup for getting and displaying the data).