WEEKS function [Date and time]

Returns the number of weeks between two dates.

Syntax 1 
WEEKS( [ datetime-expression, ] datetime-expression )
Syntax 2 
WEEKS( 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 is subtracted from the datetime value. If you supply an integer-expression, the datetime-expression must be explicitly cast as DATETIME.

Returns

Syntax 1 returns an INTEGER.

Syntax 2 returns a TIMESTAMP.

Remarks

Given a single date (Syntax 1), the WEEKS function returns the number of weeks since 0000-02-29.

Given two dates (Syntax 1), the WEEKS function returns the number of weeks between them. The WEEKS function is similar to the DATEDIFF function, however the method used to calculate the number of weeks between two dates is not the same and can return a different result. The return value for WEEKS is determined by dividing the number of days between the two dates by seven, and then rounding down; however, DATEDIFF uses number of week boundaries. This can cause the values returned to be different. For example, if the first date is a Friday and the second date is the following Monday, the WEEKS function returns a difference of 0, and the DATEDIFF function returns a difference of 1. While neither method is better than the other, you should consider the difference when choosing between WEEKS and DATEDIFF.

For more information about the DATEDIFF function, see DATEDIFF function [Date and time].

Given a date and an integer (Syntax 2), the WEEKS function adds the integer number of weeks to the specified date. This function is similar to the DATEADD function.

For more information about the DATEADD function, see DATEADD function [Date and time].

See also

For information about casting data types, see CAST function [Data type conversion].

Standards and compatibility

  • SQL/2003   Vendor extension.

Example

The following statement returns the value 8, signifying that 2008-09-13 10:07:12 is eight weeks after 2008-07-13 06:07:12.

SELECT WEEKS( '2008-07-13 06:07:12',
   '2008-09-13 10:07:12' );

The following statement returns the value 104792, signifying that the date is 104792 weeks after 0000-02-29.

SELECT WEEKS( '2008-07-13 06:07:12' );

The following statement returns the timestamp 2008-06-16 21:05:07.0, indicating the date and time five weeks after 2008-05-12 21:05:07. 

SELECT WEEKS( CAST( '2008-05-12 21:05:07'
AS TIMESTAMP ), 5);
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1