How to extract week number in sql

by

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.

More Related Contents:

  1. SQL function PIVOT with 2 column
  2. How to load a large number of strings to match with oracle database?
  3. How can I find which tables reference a given table in Oracle SQL Developer?
  4. Oracle SQL escape character (for a ‘&’)
  5. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  6. Exporting a CLOB to a text file using Oracle SQL Developer
  7. How to create id with AUTO_INCREMENT on Oracle?
  8. Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]
  9. How to handle a single quote in Oracle SQL
  10. Select count(*) from multiple tables
  11. Fetch the rows which have the Max value for a column for each distinct value of another column
  12. REGEX to select nth value from a list, allowing for nulls
  13. SQL – How to select a row having a column with max value [duplicate]
  14. Oracle: loading a large xml file?
  15. IF EXISTS condition not working with PLSQL
  16. Group by alias (Oracle)
  17. Using pivot on multiple columns of an Oracle row
  18. Sleep function in ORACLE
  19. Solution to “cannot perform a DML operation inside a query”?
  20. Split comma separated values of a column in row, through Oracle SQL query
  21. oracle — Split multiple comma separated values in oracle table to multiple rows
  22. How to query a CLOB column in Oracle
  23. update columns values with column of another table based on condition [duplicate]
  24. how to select even records from a table in oracle?
  25. Is there a version control system for database structure changes?
  26. Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
  27. How to check if a column exists before adding it to an existing table in PL/SQL?
  28. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  29. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment