CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user2133404
user2133404

Reputation: 1857

How to extract week number in sql

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?

Upvotes: 29

Views: 285290

Answers (4)

WoMo
WoMo

Reputation: 7256

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:

Parameter Explanation
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.

(See also Oracle 19 documentation on datetime format elements.)

Upvotes: 83

Sheen
Sheen

Reputation: 1

Select last_name, round (sysdate-hire_date)/7,0) as tuner 
  from employees
  Where department_id = 90 
  order by last_name;

Upvotes: 0

miz
miz

Reputation: 1

Use 'dd-mon-yyyy' if you are using the 2nd date format specified in your answer. Ex:

to_date(<column name>,'dd-mon-yyyy')

Upvotes: -1

Vinicius Lima
Vinicius Lima

Reputation: 544

Try to replace 'w' for 'iw'. For example:

SELECT to_char(to_date(TRANSDATE, 'dd-mm-yyyy'), 'iw') as weeknumber from YOUR_TABLE;

Upvotes: 5

Related Questions