Home » SQL & PL/SQL » SQL & PL/SQL » TO_DATE Function (merged 2) 10g
TO_DATE Function (merged 2) 10g [message #416826] Tue, 04 August 2009 09:23 Go to next message
salama
Messages: 36
Registered: January 2006
Member

Please take a look at the code below. I am decoding 3 codes in a database to appropriate date formats and the issue that I am experiencing is with decoding the 602 format for YYYY.

So in the database the 602 code corresponds to a date format that is four digits , for example 1999. Now when i use YYYY,
oracle converts this to 08/01/1999, so for some reason it picks August 1st. How can I convert the 1999 to 01/01/1999, so it uses the first of the year instead of August first. Can you please provide me with the actual syntax.




TO_DATE(T1.DRUGSTARTDATE,decode(DRUGSTARTDATEFORMAT,102,'YYYYMMDD',610,'YYYYMM',602,'YYYY'))
Re: TO_DATE function [message #416828 is a reply to message #416826] Tue, 04 August 2009 09:28 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
have a look at the trunc function, but I suspect you'll struggle to do this in one line of code.
You can probably do it in a single statement if you use CASE instead of DECODE.

EDIT: added final line

[Updated on: Tue, 04 August 2009 09:29]

Report message to a moderator

Re: TO_DATE Function (merged 2) 10g [message #416832 is a reply to message #416826] Tue, 04 August 2009 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>so it uses the first of the year instead of August first.
If you want Jan 01, then explicitly specify JAN 01
Re: TO_DATE Function (merged 2) 10g [message #416833 is a reply to message #416826] Tue, 04 August 2009 10:22 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Try this one which is working fine
Using Decode

DECODE(LENGTH(TO_CHAR(T1.DRUGSTARTDATE, DECODE(DRUGSTARTDATEFORMAT,602,'YYYY'))),4,
              '01/01/'||TO_CHAR(T1.DRUGSTARTDATE, DECODE(DRUGSTARTDATEFORMAT,602,'YYYY')),
        TO_CHAR(T1.DRUGSTARTDATE, DECODE(DRUGSTARTDATEFORMAT,102,'YYYYMMDD',610,'YYYYMM')))



Check out the example for the above required expression:

CREATE TABLE EMP_TEMP (  GDATE  DATE, DEPNO  NUMBER (10,2) ) ;


Records for Table
INSERT INTO EMP_TEMP VALUES (TO_DATE('11/11/2006','dd/mm/yyyy'),10);
INSERT INTO EMP_TEMP VALUES (TO_DATE('11/11/2007','dd/mm/yyyy'),20);
INSERT INTO EMP_TEMP VALUES (TO_DATE('11/11/2008','dd/mm/yyyy'),30);
COMMIT;


Query
SELECT DECODE(LENGTH(TO_CHAR(gDATE, DECODE(depno,30,'YYYY'))),4,
              '01/01/'||TO_CHAR(gDATE, DECODE(depno,30,'YYYY')),
        TO_CHAR(gDATE, DECODE(depno,10,'YYYYMMDD',20,'YYYYMM'))) AS Resultset FROM emp_temp


Query Result set
20061111
200711
01/01/2008
Re: TO_DATE Function (merged 2) 10g [message #416836 is a reply to message #416826] Tue, 04 August 2009 10:46 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
@tapaskmanna - You do realise that the OP wants to convert strings to dates and not the other way round?
Re: TO_DATE Function (merged 2) 10g [message #416838 is a reply to message #416826] Tue, 04 August 2009 12:17 Go to previous messageGo to next message
salama
Messages: 36
Registered: January 2006
Member
Thanks guys for the prompt responses. I think the way I phrased the question was a little confusing. So here is what I want to do.
The column DRUGSTARTDATE has a value of 1993, I want to decode that to 01/01/1993, however the code that I use below, decodes it
to 08/01/1993. For some reason Oracle decodes it to August 1st. Hope this is more clear. Appreciate any feedback.



TO_DATE(T1.DRUGSTARTDATE,decode(DRUGSTARTDATEFORMAT,602,'YYYY'))
Re: TO_DATE Function (merged 2) 10g [message #416839 is a reply to message #416826] Tue, 04 August 2009 12:25 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>The column DRUGSTARTDATE
What data type is DRUGSTARTDATE?

>decode(DRUGSTARTDATEFORMAT,602,'YYYY')
why is decode used within TO_DATE()?
Re: TO_DATE Function (merged 2) 10g [message #416842 is a reply to message #416826] Tue, 04 August 2009 12:56 Go to previous messageGo to next message
salama
Messages: 36
Registered: January 2006
Member
The DRUGSTARTDATE is a Data Type Date and the column has the value 1993. The DRUGSTARTDATEFORMAT has a code of 602, that corresponds to the format YYYY, hence 1993.

DRUGSTARTDATEFORMAT DRUGSTARTDATE CONVERTED VALUE

102 19991130 11/30/1999
610 200303 3/1/2003
602 1993 8/1/1993

Please let me know if i should not be using decode and if not then what else?

Re: TO_DATE Function (merged 2) 10g [message #416843 is a reply to message #416826] Tue, 04 August 2009 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>The DRUGSTARTDATE is a Data Type Date and the column has the value 1993.
You should NOT be using TO_DATE on DATE data type.

If you want to display/report DRUGSTARTDATE then use TO_CHAR() function
Re: TO_DATE Function (merged 2) 10g [message #416844 is a reply to message #416826] Tue, 04 August 2009 13:04 Go to previous messageGo to next message
salama
Messages: 36
Registered: January 2006
Member
Not sure if I am missing anything here, but if you notice when I use TO_DATE on 19991130, I get the desired conversion to 11/30/1999. How else can I convert 19991130 to 11/30/1999?
Re: TO_DATE Function (merged 2) 10g [message #416845 is a reply to message #416826] Tue, 04 August 2009 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Not sure if I am missing anything here,
You don't know, what you don't know.
TO_DATE() converts strings to DATE datatype.
Oracle can only display strings to terminal & does implicit data type conversion from DATE back to string automagically.
TO_CHAR() converts DATE data type to strings.
Re: TO_DATE Function (merged 2) 10g [message #416856 is a reply to message #416844] Tue, 04 August 2009 15:42 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Salama, cookiemonster has already said to look at the TRUNC function for DATEs. You can truncate by year, month, day, etc. It will do what you want.
Re: TO_DATE Function (merged 2) 10g [message #416864 is a reply to message #416842] Tue, 04 August 2009 17:49 Go to previous message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
salama wrote on Tue, 04 August 2009 18:56
The DRUGSTARTDATE is a Data Type Date and the column has the value 1993. The DRUGSTARTDATEFORMAT has a code of 602, that corresponds to the format YYYY, hence 1993.

DRUGSTARTDATEFORMAT DRUGSTARTDATE CONVERTED VALUE

102 19991130 11/30/1999
610 200303 3/1/2003
602 1993 8/1/1993

Please let me know if i should not be using decode and if not then what else?



Are you sure it's a Date?
Based on what you're asking and your examples I assumed it was a VARCHAR2.
If it is a varchar use trunc as previously stated.
If it is a date then you don't understand how dates work in oracle.
Previous Topic: Insert data in Oracle table from text file or Excel file
Next Topic: UTL_MAIL (merged)
Goto Forum:
  


Current Time: Wed Dec 07 02:49:00 CST 2016

Total time taken to generate the page: 0.11450 seconds