Getting the week number from a Timestamp in SQL-CodePudding

I have a field which displays a timestamp 'mm/dd/yyyy hh:mi:ss'. I am trying to extract the Isoweek number from this field unsuccessfully receiving a variant of error messages depending on the formula I have used.

Hi, I hope someone can assist.

I am attempting to get the IsoWeeknumber from a Timestamp. I have tried the below 3 revisions getting varying error messages.

to_char(to_date(Datestamp, 'dd/mm/yyyy'), 'iw')
to_char(trunc(Datestamp),'iw')
to_char(trunc(to_date(Datestamp),'iw'), 'dd/mm/yyyy hh24:mi:ss')

I have also reversed to_char(to_date(()) with no luck.

CodePudding user response：

This is how you can extract week number from date

    select to_char(to_date('01/02/2022','MM/DD/YYYY'),'WW') from dual

CodePudding user response：

As you have date and time

select to_char(to_date('01/02/2022 10:10:10','MM/DD/YYYY HH:MI:SS'),'WW') from dual

| TO_CHAR(TO_DATE('01/02/202210:10:10','MM/DD/YYYYHH:MI:SS'),'WW') |
| :--------------------------------------------------------------- |
| 01                                                               |

db<>fiddle here

CodePudding user response：

Use the same format as the string (with HH24 for a 24-hour clock, rather than HH or HH12 which are for 12-hour clocks):

SELECT to_char(to_date(Datestamp, 'MM/DD/YYYY HH24:MI:SS'), 'IW')
FROM   table_name;

db<>fiddle here