substr with to_char

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...
LVL 1
thefirstfbliAsked:
Who is Participating?
 
Jinesh KamdarConnect With a Mentor Commented:
>> 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'
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
To get feb details u can use any of the below :

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

or

select bla from x where to_number(SUBSTR(TO_CHAR(SYSDATE,'dd/mm/yyyy'),4,2)) = 2
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
>>>>>>>>>>>>>>>>>>>>>>>>
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

Thanks
0
 
konektorCommented:
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','dd.mm.yyyy') and to_date('31.01.2007 23:59:59','dd.mm.yyyy hh24:mi:ss')
0
All Courses

From novice to tech pro — start learning today.