CODEWITHSUNDEEP
asp.netoracle-databasedateformat
Handsome
Handsome

Reputation: 215

retrieve date from database as another format

I'm using oracle as my database, I have a taskdate column as TIMESTAMP. the format it save in database is 5/29/2015 10:27:04.000000 AM. can I convert it into 2015-05-29 this format when I retrieve it out??

Upvotes: 2

Views: 338

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

the format it save in database is 5/29/2015 10:27:04.000000 AM

Date/Timestamps doesn't have any format. Oracle doesn't store the date/timestamps in the format you see. The format you see is only for display purpose. Date is internally stored in 7 bytes which is Oracle's proprietary format.

can I convert it into 2015-05-29 this format when I retrieve it out??

For display:

You need to use TO_CHAR along with desired format model to display in your desired format.

For example,

SQL> SELECT SYSTIMESTAMP, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD') my_tmstmp FROM DUAL;

SYSTIMESTAMP                             MY_TMSTMP
---------------------------------------- ----------
29-MAY-15 11.40.25.054000 AM +05:30      2015-05-29

SQL>

For date-arithmetic:

For date arithmetic you should leave the data type as date itself. You could use TRUNC to truncate the time portion and leaving only the date portion:

For example,

SQL> alter session set nls_date_format='YYYY-MM-DD';

Session altered.

SQL> SELECT SYSTIMESTAMP, TRUNC(SYSTIMESTAMP) my_tmstmp FROM DUAL;

SYSTIMESTAMP                             MY_TMSTMP
---------------------------------------- ----------
29-MAY-15 11.42.41.255000 AM +05:30      2015-05-29

SQL>

Upvotes: 2

BitLord
BitLord

Reputation: 187

Of course, you can. You may either use "cast .. as date" to convert value to date, and then trunc it (in case you want to leave format as date) or use to_char (if want value as character). Final expressions for both options may be something like that:

SELECT TRUNC(CAST(column AS DATE)) FROM your_table...

or

SELECT TO_CHAR(column, 'yyyy-mm-dd') FROM your_table...

Upvotes: 1

Related Questions