WEEKS function [Date and time]

Function

Returns the number of weeks since an arbitrary starting date/time, returns the number of weeks between two specified date/times, or adds the specified integer-expression number of weeks to a date/time.

Syntax

WEEKS ( datetime-expression
| datetime-expression, datetime-expression
| datetime-expression, integer-expression )

Parameters

datetime-expression A date and time.

integer-expression The number of weeks to be added to the datetime-expression. If integer-expression is negative, the appropriate number of weeks are subtracted from the date/time value. Hours, minutes, and seconds are ignored. If you supply an integer expression, the datetime-expression must be explicitly cast as a DATETIME data type.

For information on casting data types, see “CAST function [Data type conversion]”.

Examples

The following statement returns the value 104278:

SELECT WEEKS( '1998-07-13 06:07:12' ) FROM iq_dummy

The following statement returns the value 9, to signify the difference between the two dates:

SELECT WEEKS( '1999-07-13 06:07:12',
	'1999-09-13 10:07:12' ) FROM iq_dummy

The following statement returns the timestamp value 1999-06-16 21:05:07.000:

SELECT WEEKS( CAST( '1999-05-12 21:05:07'
AS TIMESTAMP ), 5) FROM iq_dummy

Usage

Weeks are defined as going from Sunday to Saturday, as they do in a North American calendar. The number returned by the first syntax is often useful for determining if two dates are in the same week.

WEEKS ( invoice_sent ) = WEEKS ( payment_received ) FROM iq_dummy

In the second syntax, the value of WEEKS is calculated from the number of Sundays between the two dates. Hours, minutes, and seconds are ignored. This function is not affected by the DATE_FIRST_DAY_OF_WEEK option.

Standards and compatibility