Query giving me two different results

[Adeel Ansari]

Its Oracle.

select TO_CHAR(SYSDATE, 'DD-Mon-YYYY Dy'),
       TO_CHAR(SYSDATE, 'D-Mon-YYYY Dy')
from DUAL

Tried one "M" as "D-M-YYYY Dy", didn't work. However, One "D" is working but giving fake result.

Try this out. And please let me know that how can we get the format, "1-Apr-2005" instead of "01-Apr-2005".

[Horatio Westock]

select TO_CHAR(SYSDATE, 'DD-Mon-YYYY Dy'),
       TO_CHAR(SYSDATE, 'D-Mon-YYYY Dy')
from DUAL

It appears that you can use the prefix FM to suppress zeros and spaces in a particular field patter.

e.g.

'FMDD-Mon-YYYY'

Supposedly gives you 9 instead of 09, for example.

I haven't tried it as I don't have Oracle. The article I got this from.
[ April 07, 2005: Message edited by: Horatio Westock ]

[Shailesh Chandra]

adeel,

You are just confused with output. This is perfectly correct

in to_char with date whenever you write two D (DD) oracle consider it as you want date in output but when you write one D (a single D) Oracle consider it as you want the Day of week.

Like if we execute this query today you will get value of
TO_CHAR(SYSDATE, 'D-Mon-YYYY Dy') as "5-Apr-2005 Thu" however today is 7th April.

here 5 means thursday is 5th day of week starting from sunday as 1 to Saturday as 7


It is something like we do in SimpleDateFormat where if we write "M" then it is month and "m" is minute

Hope it is clear now

thanks
[ April 07, 2005: Message edited by: Shailesh Chandra ]

[Shailesh Chandra]

Adeel,

here Horatio has given correct suggestion

and this query will give you desired result

SELECT TO_CHAR(SYSDATE, 'FMDD-Mon-YYYY') from dual

Shailesh

[Adeel Ansari]

Love you mates.

I have tried some of the docs though, but never encountered a single "D". May be i just passed that without seeing. However, "FM" is really out of my thoughts.

Thanks a million.
[ April 07, 2005: Message edited by: Adeel Ansari ]

[Adeel Ansari]

Hey mates, it is not giving me zero even for the month, in the query below.

SELECT TO_CHAR(SYSDATE, 'FMDD-MM-YYYY') from dual

You know first I tried it with "Mon", like

SELECT TO_CHAR(SYSDATE, 'FMDD-Mon-YYYY') from dual

and then thought to remove zero from the month part too and did this

SELECT TO_CHAR(SYSDATE, 'FMDD-FMMM-YYYY') from dual


and an error occured.

Finally, I got the desired result without adding "FM" in the month part. It means to me that adding FM is not something related to "DD", it is just for removing those zeros from the whole date string. Bottomline is If we dont want those zeros in DD, MM, YYYY, just add "FM" in the start.

Thanks for the info once again.

[Shailesh Chandra]

Originally posted by Adeel Ansari:

and then thought to remove zero from the month part too and did this

SELECT TO_CHAR(SYSDATE, 'FMDD-FMMM-YYYY') from dual

and an error occured.

adeel,

This query doesn't give and error to me

A "FM" is a format mask which means when a date will be converted into given format all zero will be supressed however if there is again one more format mask ("FM") then onwards zero will not be supressed and result of above query will be "7-04-2005"

can you tell what error occured ?

shailesh

[Shailesh Chandra]

Hi adeel,

might be my this post does not relates to this thread but I felt to put this information here.

Instead of "YYYY" start using "RR" this was one recommendation given to me long back.

Since you are using format mask it may happen that some where you format your date as "YY" not "YYYY" then "FM" may would change year to "5" and later on reverse coversion from char to date using to_date may cause problem.

See the differnce of results in given query

 
SELECT TO_DATE(TO_CHAR(SYSDATE, 'FMDD-FMMM-YY'),'DD-MM-YYYY') FROM DUAL
 
SELECT TO_DATE(TO_CHAR(SYSDATE, 'FMDD-FMMM-RR'),'DD-MM-RRRR') FROM DUAL
 

thanks
[ April 07, 2005: Message edited by: Shailesh Chandra ]

[Adeel Ansari]

Originally posted by Shailesh Chandra:
adeel,

This query doesn't give and error to me

Yeah, pardon. May be I did something wrong at that time. Now it got executed fine. It is the same, the first occurance means "off zeros" second "on zeros" then third again "off zeros" and so forth.

And yes I know about the RR stuff . Thanks for the info, anyways.
Actually all the problems and solutions regarding "RR" are quite clearly defined in the docs. On the other hand couldn't find that "FM", format mask, thing in the docs at that time.

[Shailesh Chandra]

happy to know that every thing is on its way

shailesh