The first subquery shown generates a list of dates starting with January 1 of the current year and ends 365 days later. week numbering system is WW, in either upper or lower case. © Copyright 2014, Oracle and/or its affiliates. In Oracle syntax the format specifier for the U.S. Then WeekDay returns 5, which corresponds to Thursday. (Assume the current date is Wednesday, July 5, 2009.)įirst the DateAdd function adds one day to the current date, resulting in a date of Thursday, July 6, 2009. The DayName function extracts the name of the day of the week from a given date. WeekDay is most often used with the DayName function. The system returns the number of the day of the week, from 1 to 7, as shown here:

You need to multiple the IW number by 7 which you can convert to a date with the day of year mask. If the Locale INI option offers no value, the system defaults to USD (United States/English). SQL> However, it's easy enough to generate a range for the the IW week number. SET DATEFIRST 1 - Set the first day of the week to Monday select dbo.FiscalWeek ('04',getdate ()) The '04' parameter is because in my experience April is the most common starting month, but you can pass in '01' to get ISO8601 week numbers, or whichever month number suits your needs. If you omit this parameter, the system checks the Locale INI option. The default is the current date.Įnter a valid date format that describes the format used by your entry in the Date parameter. The system assumes your entry is in the format specified by the Format parameter. Use this function to determine the day of the week in a given date and return the value as a number. You are here: Function Reference > Alphabetical Listing > W > WeekDay WeekDay

Oracle sql week number