I want to print the timestamp from the below sql
select to_date('01/01/2011 12:00:00 AM','dd/mm/yyyy hh:mi:ss AM') from dual;
current output --> 1/1/2011 (not printing the timestamp only for 12 am. if the min is 12:01 then it is printing.
but I need the output as 1/1/2011 12:00:00 AM
TO_DATE converts a string to a DATE. A DATE is stored in a packed binary format that is not human readable. An Oracle DATE does not have a format. So when you ask a program to display a date, it has to then convert the DATE to a string. If you don't explicitly specify the format by doing an explicit TO_CHAR, a tool like SQL*Plus will convert the date to a string using the session's NLS_DATE_FORMAT. Other applications may choose different ways to convert a date to a string-- using the client's regional settings, for example, or by allowing the user to configure the format.
If you want to return a string in a particular format that represents a DATE, you'd need to use an explicit TO_CHAR. Something like
SELECT to_char( some_date_column, 'dd/mm/yyyy hh:mi:ss AM' )
FROM some_table
In the specific case you posted, since you have the string in your hand as a string, you'd simply want to select it from dual rather than doing a TO_DATE to convert it to a date and then a TO_CHAR to convert it back to a string. I'm assuming, though, that you have an actual DATE in the actual table that you are trying to select from.
The best way to control the formatting is to use to_char and explicitly specify the date format you want.
select to_char(to_date('01/01/2011 12:00:00 AM','dd/mm/yyyy hh:mi:ss AM'),'DD/MM/yyyy hh:mi:ss AM')
from dual;
You can Use
select Convert(varchar,'01/01/2011 12:00:00 AM',113)
