CODEWITHSUNDEEP
sqloracle-databasedateto-date
user1090616
user1090616

Reputation: 11

to_date unable to print timestamp

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

Upvotes: 1

Views: 4152

Answers (3)

Justin Cave
Justin Cave

Reputation: 231671

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.

Upvotes: 4

Peter
Peter

Reputation: 12711

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;

Upvotes: 1

AMH
AMH

Reputation: 6451

You can Use 

select  Convert(varchar,'01/01/2011 12:00:00 AM',113)

Upvotes: -3

Related Questions