Getting the week number from a Timestamp in SQL

Question

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.

What is the **data type** of column `datestamp`? Don't guess; run `describe ` and see what data type it reports for this column. Also, as an aside (but not "instead of" what I just said), you should include the error messages in your question - they provide lots of clues. — , Jun 27 '22 at 14:46
Are you sure? `hh:mi:ss` means 12-hour format, which would be ambiguous without AM/PM — Wernfried Domscheit, Jun 27 '22 at 15:59

score 2 · Accepted Answer · answered Jun 27 '22 at 14:11

2

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

answered Jun 27 '22 at 14:11

MT0

143,790
11
59
117

score 0 · Answer 2 · answered Jun 27 '22 at 14:03

0

This is how you can extract week number from date

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

answered Jun 27 '22 at 14:03

TouseefAwan

56
6

nbk · Answer 3 · 2022-06-27T16:07:17.647

0

As you have date and time

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

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

db<>fiddle here

edited Jun 27 '22 at 16:07

answered Jun 27 '22 at 14:10

nbk

45,398
8
30
47

Note, Week according to ISO-8601 `IW` is different to `WW` which gives *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.* – Wernfried Domscheit Jun 27 '22 at 15:57
thx i didn't see tha, but as solution it was still ok – nbk Jun 27 '22 at 16:08

Getting the week number from a Timestamp in SQL

3 Answers3