week/isoweek functions

These functions return the corresponding week/isoweek from a timestamp. The timestamp must be supplied as an argument to the function.

Syntax:

INTEGER week(<timestamp>)

INTEGER isoweek(<timestamp>)

Semantics:

• timestamp: These functions expects a timestamp as the input argument.
• Return Value: INTEGER

  Table 12-4 Timestamp week extract functions

  Function	Return Value
  week	Returns the week number within the year where a week starts on Sunday and the first week has a minimum of 1 day in this year, in the range 1 ~ 54.
  isoweek	Returns the week number within the year based on ISO-8601, where a week starts on Monday and the first week has a minimum of 4 days in this year, in range 0 ~ 53.

Note:

If the argument is NULL or empty, the result is also NULL or empty.

Example 12-25 Determine the week and ISO week number from a passenger's travel date

SELECT
$s.fullName,
$s.contactPhone, 
week(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS TravelWeek,
isoweek(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS ISO_TravelWeek
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explanation: You first use CAST expression to convert the flightDate to a TIMESTAMP and then fetch the week and isoweek from the timestamp.

Output:

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","TravelWeek":7,"ISO_TravelWeek":7}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","TravelWeek":5,"ISO_TravelWeek":5}