WEEKS( timestamp-expression )

WEEKS( timestamp-expression, timestamp-expression )

WEEKS( timestamp-expression, integer-expression )

INTEGER with Syntax 1 or Syntax 2.

TIMESTAMP with Syntax 3.

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

Given two dates (Syntax 2), 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 in its computation. This can cause the values returned from the two functions 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, but 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 3), the WEEKS function adds the integer number of weeks to timestamp-expression. With Syntax 3, you must explicitly cast timestamp-expression as a TIME, DATE, or TIMESTAMP data type. If timestamp-expression is a TIME value, the current date is assumed. Instead of Syntax 3, use the DATEADD function.

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

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

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 value 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);