I have a transdate column of varchar2 type which has the following entrees
01/02/2012
01/03/2012
etc.
I converted it in to date format in another column using to_date function. This is the format i got.
01-JAN-2012
03-APR-2012
When I'm trying to extract the weekno, i'm getting all null values.
select to_char(to_date(TRANSDATE), 'w') as weekno from tablename.
null
null
How to get weekno from date in the above format?
This question is related to
sql
oracle
oracle-sqldeveloper
Try to replace 'w' for 'iw'. For example:
SELECT to_char(to_date(TRANSDATE, 'dd-mm-yyyy'), 'iw') as weeknumber from YOUR_TABLE;
Use 'dd-mon-yyyy'
if you are using the 2nd date format specified in your answer. Ex:
to_date(<column name>,'dd-mon-yyyy')
Select last_name, round (sysdate-hire_date)/7,0) as tuner
from employees
Where department_id = 90
order by last_name;
Source: Stackoverflow.com