Home » SQL & PL/SQL » SQL & PL/SQL » to_char function help
to_char function help [message #384026] Sun, 01 February 2009 22:19 Go to next message
auto
Messages: 8
Registered: January 2009
Location: aus
Junior Member
Hi I have added_dt column which is a varchar(8) type and i need to pick the year and check it with sysdate-1

And the column has few null entries with fromat 00000000. so i tried to use nvl. But i am getting


ORA-01722 error.

nvl(TO_CHAR('YYYY',start_dt),00) = TO_CHAR('YYYY',SYSDATE-1)

Help much appreciated. Thank you!


[EDITED by LF: disabled smilies in this message]

[Updated on: Tue, 03 February 2009 13:59] by Moderator

Report message to a moderator

Re: to_char function help [message #384030 is a reply to message #384026] Sun, 01 February 2009 22:35 Go to previous messageGo to next message
st33chen
Messages: 11
Registered: January 2009
Junior Member
hi,

how about

TO_CHAR('YYYY',NVL(start_dt,0))
Re: to_char function help [message #384032 is a reply to message #384026] Sun, 01 February 2009 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>nvl(TO_CHAR('YYYY',start_dt),00)
>TO_CHAR('YYYY',SYSDATE-1)
>TO_CHAR('YYYY',NVL(start_dt,0))
NONE are valid

TO_CHAR() function is used against DATE datatypes!
TO_CHAR(sysdate,'YYYY') -- Is a valid use of TO_CHAR

It appears folks are unwilling or incapable to RTFM
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions180.htm#sthref2295

[Updated on: Sun, 01 February 2009 22:44]

Report message to a moderator

Re: to_char function help [message #384038 is a reply to message #384026] Sun, 01 February 2009 23:18 Go to previous messageGo to next message
auto
Messages: 8
Registered: January 2009
Location: aus
Junior Member
Blackswan, you have pointed me to a document with to_char function. I have started working on orcale recently. I think most of the people come to forums because they are in a rush to find something quickly. and if someone knows the solution on top of their head... it will be a help for the poster. when people have time to spare, i am sure they all love to open the books, documents and read. You may be bored reading the same questions over and over but for the poster its a first time question. and he is perhaps hoping for a short simple solution because he has hundred other problems to finish today at work.
so please if you have a quick answer and if you want to point me to the extra documntation for future reference. thats great! but just pointing me to a to_char function notes when you yourself told thats not the way to do! hmmm.. Confused
Re: to_char function help [message #384039 is a reply to message #384026] Sun, 01 February 2009 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

>Hi I have added_dt column which is a varchar
First mistake.
Date should be stored as DATE datatypes; NOT VARCHAR2
You can not to arithmetic on characters; 'A'+'B' make no sense.

Within Oracle arithmetic operations against DATE datatypes works.

> i need to pick the year and check it with sysdate-1
While you may understand what this means, I do not so I don't know what spoon fed answer you desire.




Re: to_char function help [message #384057 is a reply to message #384038] Mon, 02 February 2009 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
auto wrote on Mon, 02 February 2009 06:18
Blackswan, you have pointed me to a document with to_char function. I have started working on orcale recently. I think most of the people come to forums because they are in a rush to find something quickly. and if someone knows the solution on top of their head... it will be a help for the poster. when people have time to spare, i am sure they all love to open the books, documents and read. You may be bored reading the same questions over and over but for the poster its a first time question. and he is perhaps hoping for a short simple solution because he has hundred other problems to finish today at work.
so please if you have a quick answer and if you want to point me to the extra documntation for future reference. thats great! but just pointing me to a to_char function notes when you yourself told thats not the way to do! hmmm.. Confused

Hire a tutor.
We think that people who come here actually want to learn something, so we like to point them to some documentation where they can find their answer.
This is particularly true when people ask fuzzy questions.

Now to get back to your question:
- why do you think 000000 can be spotted using nvl? nvl is used for NULL values, not for some value that might mean something in your application. Use DECODE to filter those out.
- If you would take the time to scan the documentation for TO_CHAR you would see what parameters it takes and the order to use them in.
Re: to_char function help [message #384213 is a reply to message #384026] Mon, 02 February 2009 20:02 Go to previous messageGo to next message
auto
Messages: 8
Registered: January 2009
Location: aus
Junior Member
Hi I have figured the code. Anyone looking for a similar issue please find below my solution.

I have used substr function to extract the months from the added_dt column since its a varchar type data.

Have used add_months function to subtarct 1 month out of SYSDATE.

And to_char function to extract months from SYSDATE.

Below is the code.

SUBSTR(start_dt,5,2) = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM')

Hope this helps.



Re: to_char function help [message #384214 is a reply to message #384026] Mon, 02 February 2009 20:13 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help

Post DDL to create table(s)
Post DML to populate test data
Post expected/desired results

>And the column has few null entries with fromat 00000000.
>SUBSTR(start_dt,5,2) = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM')
Which month is 00?

AGAIN, it is a bad, bad implementation to store dates in VARCHAR2.
If you had used DATE datatype, it would have prevented the polluted & bad data in the existing table.

Re: to_char function help [message #384215 is a reply to message #384026] Mon, 02 February 2009 21:19 Go to previous messageGo to next message
auto
Messages: 8
Registered: January 2009
Location: aus
Junior Member
I am just working on a database that is created by someone else so i dont have a choice with varchar date. But I will recommend it to them.

One of my criteria is as below

TO_CHAR(SYSDATE,'MM') > 1

so my select statement should avoid 0 entries anyway.
Re: to_char function help [message #384217 is a reply to message #384026] Mon, 02 February 2009 21:39 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>i need to pick the year and check it with sysdate-1
Again, I am not clear what above means, but I know it is not the same as "TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM')"

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help


Post DDL to create table(s)
Post DML to populate test data
Post expected/desired results

[Updated on: Mon, 02 February 2009 21:40]

Report message to a moderator

Re: to_char function help [message #384344 is a reply to message #384215] Tue, 03 February 2009 08:19 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
auto wrote on Mon, 02 February 2009 22:19

TO_CHAR(SYSDATE,'MM') > 1



TO_CHAR returns as character string.
1 is a number.

See a problem?

You are working with a poorly designed system. Ok, understood. Not your fault. But now you are only exacerbating the problem with the same poor coding.
Re: to_char function help [message #384359 is a reply to message #384026] Tue, 03 February 2009 08:56 Go to previous message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ignoring joy_division's valid point for a second

Are you sure you mean this:
TO_CHAR(SYSDATE,'MM') > 1 


Because that says to me this is select statement that'll never work in the month of January.
Previous Topic: A list of Mounth
Next Topic: How to Debugg
Goto Forum:
  


Current Time: Wed Dec 07 14:51:47 CST 2016

Total time taken to generate the page: 0.11144 seconds