substr with to_char

Posted on 2007-10-02
Last Modified: 2013-12-19
SELECT bla blaa FROM X WHERE substr( x_id,4,10) = 'ACCOUNT'

is working..

SELECT bla blaa FROM x WHERE  substr(date,1,2) = '12'
is also working

SELECT bla blaa FROM x WHERE  substr(date,4,5) = '02'

is not ?

date format is  12/02/1998 in table and data type is DATE..  

in addition to these real problem is to do this,

SELECT bla blaa FROM X WHERE To_Char (date, 'MONTH')  AND  SubStr (date,4,6) = 'Feb' )

i can do it with adter to char LIKE 'FEB%' but i want to use to_char and substr together...
Question by:thefirstfbli
    LVL 28

    Expert Comment

    To get feb details u can use any of the below :

    select bla from x where to_char(date,'MON') = 'FEB'


    select bla from x where to_number(SUBSTR(TO_CHAR(SYSDATE,'dd/mm/yyyy'),4,2)) = 2
    LVL 28

    Assisted Solution

    SELECT bla blaa FROM x WHERE  substr(date,4,5) = '02'

    is not ?

    SELECT bla blaa FROM x WHERE  substr(date,4,2) = '02'  --> second argument is how many chars do you need , so we have to use 2 not 5

    LVL 9

    Expert Comment

    you should use to_char to convert date into string before using substr

    select ... from ... where substr(to_char(date_column),'dd/mm/yyyy'),4,2) = '01'

    you shuld also use

    select ... from ... where trunc(date_column,'mm') = 1

    if you have some indexes on date_column, you should not use any function over it. if you want to select all january records

    select ... from ... where date_column between to_date('01.01.2007','') and to_date('31.01.2007 23:59:59',' hh24:mi:ss')
    LVL 18

    Accepted Solution

    >> SELECT bla blaa FROM x WHERE  substr(date,4,5) = '02' is not ?
    The last parameter in the SUBSTR denotes the no. of chars to be fetched, not the end position within the string. This should work.

    SELECT bla blaa FROM x WHERE substr(date,4,2) = '02'

    >> SELECT bla blaa FROM X WHERE To_Char (date, 'MONTH')  AND  SubStr (date,4,6) = 'Feb' )
    Use this.

    SELECT bla blaa FROM X WHERE SUBSTR(To_Char(date, 'MONTH'),1,3) = 'FEB'

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now