Search code examples
sqloracledatetimehyperion

Getting the week number from a Timestamp in SQL

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.

Solution

  • 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