Home » SQL & PL/SQL » SQL & PL/SQL » Hard coding month and day to a year
Hard coding month and day to a year [message #267757] Fri, 14 September 2007 13:50 Go to next message
jpockets
Messages: 5
Registered: September 2007
Junior Member
Hi everyone new to oracle and i'm having a problem:

I have this function that subtracts 1 from the previous year. to that i want to add '-12-31' so it would be 'YYYY-MM-DD'.

Function:

When To_char(b.acct_dt,'MM')='08'
    then cast(to_char(to_number(to_char(b.acct_dt, 'yyyy') - 1)) as Varchar(4)) +'-12-31'

    else TO_CHAR(b.acct_dt, 'YYYY-MM-DD HH24:MI:SS')
    end NewLedgerDate


The error i'm getting is: inconsitent datatypes: expected number got char, is there a way to do this?

Thanks for the help..
Re: Hard coding month and day to a year [message #267758 is a reply to message #267757] Fri, 14 September 2007 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>is there a way to do this?
Yes.
The plus operator ("+") is NOT how you concatenate strings together.
You should be using "||" operator, instead.
What happens when you subtract a numeric "1" from a string? "to_char(b.acct_dt, 'yyyy') - 1"?

I suggest you do not use CAST; but that is your choice.

Re: Hard coding month and day to a year [message #267759 is a reply to message #267757] Fri, 14 September 2007 14:00 Go to previous messageGo to next message
jpockets
Messages: 5
Registered: September 2007
Junior Member
Thanks for the help, it worked, wasn't sure on how to do that.

How does it look now:

Case
When To_char(b.acct_dt,'MM')='08'
    then to_char(to_number(to_char(b.acct_dt, 'yyyy') - 1))  ||'-12-31'

    else TO_CHAR(b.acct_dt, 'YYYY-MM-DD HH24:MI:SS')
    end NewLedgerDate
Re: Hard coding month and day to a year [message #267762 is a reply to message #267757] Fri, 14 September 2007 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is part of a real issue and I'm pretty sure this is not the correct way to do it.

Regards
Michel
Re: Hard coding month and day to a year [message #267763 is a reply to message #267757] Fri, 14 September 2007 14:18 Go to previous messageGo to next message
jpockets
Messages: 5
Registered: September 2007
Junior Member
do you have any advice on how to correct way to do this
Re: Hard coding month and day to a year [message #267765 is a reply to message #267763] Fri, 14 September 2007 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry my crystall ball is broken, I don't any idea of what the real issue is.

Regards
Miche:l
Re: Hard coding month and day to a year [message #267766 is a reply to message #267757] Fri, 14 September 2007 14:25 Go to previous messageGo to next message
jpockets
Messages: 5
Registered: September 2007
Junior Member
the case statement is working, so it can't be that wrong.
Re: Hard coding month and day to a year [message #267767 is a reply to message #267757] Fri, 14 September 2007 14:30 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
One thing I see, and depending on business rules it might not be an issue at all, is when you are putting the 12-31 date in the time stamp gets truncated.
Re: Hard coding month and day to a year [message #267768 is a reply to message #267766] Fri, 14 September 2007 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Syntaxically correct or even giving the expected result does not mean it is functionally correct nor it is the correct way to do it.

It was just a warning, now it's up to you if you want to think this is good, then it is good for me.

Regards
Michel
Re: Hard coding month and day to a year [message #267769 is a reply to message #267757] Fri, 14 September 2007 14:42 Go to previous messageGo to next message
jpockets
Messages: 5
Registered: September 2007
Junior Member
Thanks for the help, i will do some more research and see what i find....
Re: Hard coding month and day to a year [message #267798 is a reply to message #267769] Sat, 15 September 2007 01:06 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
To get december 31st of last year using standard date functions you can use
trunc(your_date, 'YYYY') - 1

This first truncates your_date to the year (meaning setting it back to 01-01 of this year) and then subtracts one day
Previous Topic: First Day Of A Given Week
Next Topic: what is the size of date datatype?
Goto Forum:
  


Current Time: Wed Dec 07 12:56:56 CST 2016

Total time taken to generate the page: 0.09753 seconds