How to extract week number in sql

Question

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

User · Answer

Try to replace  w  for  iw   For example   SELECT to char to date TRANSDATE   dd-mm-yyyy     iw   as weeknumber from YOUR TABLE

User · Answer

Select last name  round  sysdate-hire date  7 0  as tuner    from employees   Where department id   90    order by last name

User · Answer

After converting your varchar2 date to a true date datatype  then convert back to varchar2 with the desired mask   to char to date  01 02 2012   MM DD YYYY    WW     If you want the week number in a number datatype  you can wrap the statement in to number     to number to char to date  01 02 2012   MM DD YYYY    WW      However  you have several week number options to consider   WW  Week of year  1-53  where week 1 starts on the first day of the year and continues to the seventh day of the year  W   Week of month  1-5  where week 1 starts on the first day of the month and ends on the seventh  IW  Week of year  1-52 or 1-53  based on the ISO standard

User · Answer

Use  dd-mon-yyyy  if you are using the 2nd date format specified in your answer  Ex   to date  lt column name gt   dd-mon-yyyy

[sql] How to extract week number in sql

Examples related to sql

Examples related to oracle

Examples related to oracle-sqldeveloper