CODEWITHSUNDEEP
sqloracle-databasedatecalendar
Rob Morris
Rob Morris

Reputation: 137

How to show week numbers as dates in Oracle SQL?

Is there a way of getting Oracle SQL to display the week number as a date (say of the Monday of that week)?

Using very basic code I've got:

select
job.job_number,
TO_CHAR(job.actual.start_date, 'IW') as WEEK_ON_SITE

from
job

Which returns:

1234    1
1235    1
1236    2
1237    3
1238    3

What I would like to get to is something like:

1234    wc 1st April
1235    wc 1st April
1236    wc 8th April
1237    wc 15th April
1238    wc 15th April

Is it just a case of adding some extra text to the TO_CHAR statement or is it more substantial than this?

Upvotes: 1

Views: 54

Answers (1)

GMB
GMB

Reputation: 222432

You can truncate the date to week to get the first day of the week:

select
    job.job_number,
    trunc(job.actual.start_date, 'IW') as week_on_site
from job

Then you can format it as needed:

select
    job.job_number,
    'wc ' || to_char(trunc(job.actual.start_date, 'IW'), 'd month')  as week_on_site
from job

Upvotes: 4

Related Questions