What exactly does trunc(date, 'IW')?

sql oracle oracle11g date-formatting
Phate picture Phate · Sep 16, 2015 · Viewed 39.1k times · Source

For my project I need to have an absolute numerical correspondence between days of the week and 1...7 values.

As you probably know the association between days and numbers can vary according to the locale, for example in Germany Monday is 1 and Sunday is 7, while in US Monday is 2 while Sunday is 1.

So, searching for a solution, I found the following code which seems working regardless of the locale, assigning Monday=1...Sunday=7:

1 + TRUNC (date) - TRUNC (date, 'IW')

Can someone explain me how does it work? In particular I just can't understand what this instruction:

TRUNC (date, 'IW')

exactly does.

Answer

John picture John · Feb 21, 2017

TRUNC(DATE,'IW') returns the first day of the week. For me TRUNC(SYSDATE,'IW) returns Monday. Today is Tuesday Feb 21. Subtract from that TRUNC(SYSDATE,'IW') which would be Monday the 20th, and you'll get 1 (because 21-20=1). Add 1 onto that as you do in the beginning of your equation and you get 2, which we associate with Tuesday.

Related questions

SQL date conversion results to "invalid number format model parameter."

I have to select some data from Oracle 11g database, but I can't seem to figure out why following select query is failing: SELECT INFO_ID, INFO_DETAIL, IMPORTANT_FLG, DELETE_FLG, CREATE_TIME, DISPLAY_ORDER FROM TABLE_NAME WHERE …

Read More
How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?

Read More
Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. The problem is that to drop a constraint for the column of a particular table I need to know …

Read More