CODEWITHSUNDEEP
stringoracle-databasedateseconds
Willi Fischer
Willi Fischer

Reputation: 455

oracle date to string

I have this problem: I want to convert the sysdate to string, using fillmode on month, day and hour only. However,

 select to_char(sysdate, 'fmmm/fmdd/yyyy fmhh12:mi:ss am') from dual

gives me results like

11/13/2013 9:45:**0** am

although it should be

11/13/2013 9:45:**00** am

any thoughts? Thanks in advance

Upvotes: 2

Views: 2519

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You shouldn't use the FM format model, because FM, as written in the documentation:

FM - Used in combination with other elements to direct the suppression of leading or trailing blanks

So using FM will make your final string shorter, if possible.

You should remove the FM from your format model mask and it will work as you expect:

select to_char(TRUNC(sysdate), 'mm/dd/yyyy hh12:mi:ss am') from dual;

Output:

11/13/2013 12:00:00 am.

I've changed my answer after reading Nicholas Krasnov's comment (thanks).

More about date format models in Oracle Documentation: Format models

Edit

Yes, the code I provided would return, for example, 01-01-2013. If you want to have the month and day without leading zeroes, than you should write it like this: fmDD-MM-YYYY fmHH:MI:SS.

The first fm makes the leading zeroes be truncated. The second fm turns off that feature and you do get leading zeroes for the time part of the date, example:

SELECT TO_CHAR(
         TO_DATE('01-01-2013 10:00:00', 'DD-MM-YYYY HH12:MI:SS'),
         'fmmm/dd/yyyy fmhh12:mi:ss am')
FROM dual;

Output:

1/1/2013 10:00:00 am.

Upvotes: 4

Related Questions