Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query giving me two different results

 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Its Oracle.



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
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Adeel,

here Horatio has given correct suggestion

and this query will give you desired result




Shailesh
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey mates, it is not giving me zero even for the month, in the query below.



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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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




thanks
[ April 07, 2005: Message edited by: Shailesh Chandra ]
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
happy to know that every thing is on its way

shailesh
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic