user2133404 user2133404 - 9 months ago 65
SQL Question

How to extract week number in sql

I have a transdate column of varchar2 type which has the following entrees



I converted it in to date format in another column using to_date function. This is the format i got.


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.


How to get weekno from date in the above format?

Answer Source

After converting your varchar2 date to a true date datatype, then convert back to varchar2 with the desired mask:


If you want the week number in a number datatype, you can wrap the statement in to_number():


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.