datestring and datestring_gmt convert timestamps or datetimes from internal to external date-time representation. The external representation is a human-readable ASCII string of up to 30 characters.
The external format is: YYYY-MM-DD hh:mm:ss uuuuuu where uuuuuu represents microseconds.
Using cast (dt as varchar) is preferred over datestring.
datestring and datestring_GMT return a varchar.
Get current date in human-readable form.
SQL> select datestring(now()); callret VARCHAR _______________________________________________________________________________ 2001-03-01 12:49:59.000000 1 Rows. -- 585 msec.
Get orders shipped later than July 3, 1996
SQL> select left (datestring(ShippedDate), 10) from Orders where ShippedDate > stringdate ('1996.6.3'); callret VARCHAR _______________________________________________________________________________ 1996-06-04 1996-06-04 1996-06-05 1996-06-05 1996-06-05 5 Rows. -- 3 msec.
The discussion of TIMESTAMP datatype in section Database Concepts of the documentation.