14:09

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Transact-SQL string and datetime functions

T-SQL Datetime functions

The first day of the week page 2

@@DATEFIRST function

@@DATEFIRST returns an integer which defines the first day of a week for the current session. In so doing, 1 is corresponding to Monday, but 7 - to Sunday respectively. I.e. if

Console

Execute

SELECT @@DATEFIRST;

returns 7, the first day of a week will be Sunday (in according with current site's settings).

To make our solution to the task be independent on the first-day-of-week settings, we need to use @@DATEFIRST function, for example

Console

Execute

WITH num(n) AS(
SELECT 0 
UNION ALL
SELECT n+1 FROM num 
WHERE n < 6),
dat AS (
SELECT DATEADD(dd,  n,  CAST('2013-01-01' AS DATETIME)) AS day FROM num
)
SELECT day, DATENAME(dw, day) week_day FROM dat  WHERE DATEPART(dw, day) = 
1+(8-@@DATEFIRST) % 7;

day	week_day
2013-01-07	Monday

To change value of the first day of a week (for a current session), we can use SET DATEFIRST statement. So, if we run the following script

SET DATEFIRST 1;
SELECT @@DATEFIRST;

we'll obtain 1, i.e. week starts with Monday now.

The first-day-of-a-week settings may be changed through the changing of session language. For example, running

SET LANGUAGE us_english;
SELECT @@DATEFIRST;

lead us to 7 (Sunday) once more, as this value is default for U.S. English, whereas choosing German will do Monday to be the first day of a week.

Nevertheless you can change the language parameter and first day of a week simultaneously to obtain, say, English and a week that will start with Monday:

SET DATEFIRST 1;
SET LANGUAGE us_english;
SELECT @@DATEFIRST;

Language settings impact specifically on the string representation of date/time components. Compare these results:

DECLARE @dt DATE = '2012-12-17'; -- December 17, 2012
SET LANGUAGE us_english;
SELECT DATENAME(DW, @dt) AS day_of_week, DATENAME(MONTH,@dt) AS month;

day_of_week	month
Monday	December

SET LANGUAGE german;
SELECT DATENAME(DW, @dt) AS day_of_week, DATENAME(MONTH, @dt) AS month;

day_of_week	month
Montag	Dezember

Conclusion. If you want to have queries that always give correct results for any settings of server or database, you can specify necessary setting parameters for the session in which these queries are executing; or you need to check the values of corresponding parameters (@@DATEFIRST as example) in each such a query.

Особенности: MSSQL

You can solve this problem (First occurrence of a Monday in January 2013) without use of @@DATEFIRST with aid of method proposed by Itzik Ben-Gan [8]. The idea of the solution is based on the following:

1. January 1, 1900 was a Monday
2. The number of days between two similar days of the week is divisible by seven

declare  @anchor_date datetime
declare  @reference_date datetime
SELECT @anchor_date='19000101', @reference_date='20130505'
SELECT DATEADD(day, DATEDIFF(day, @anchor_date,
DATEADD(year, DATEDIFF(year, '19000101', @reference_date), '19000101') - 1) /7*7 + 7,
@anchor_date);

Автор: V.I.Gershovich

Особенности: MSSQL

It is possible to mention one more way to solve this task without use of @@DATEFIRST. The method is based on comparison of any function DATEPART/DATENAME with the same function taking the date being Monday as parameter. For example, it may be January 1st, 1900 (already mentioned).
Then the initial query in this chapter would look like as

SELECT day FROM dat  WHERE DATEPART(dw, day) = DATEPART(dw, '19000101');

or as

SELECT day FROM dat  WHERE DATENAME(dw, day) = DATENAME(dw, '19000101');

Автор: P.A.Kurochkin

Seggested exercises: 78, 99, 110, 118

Pages

DATEADD DATEPART DATENAME SET DATEFIRST first day of the week SET LANGUAGE Itzik Ben-Gan

Previous [DATENAME function]

[DATEFROMPARTS function] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
several days ago

https://exchangesumo.com/obmen/CNTEUR-NIXEUR/

SQL Problems and solutions

S. I. Moiseenko

Transact-SQL string and datetime functions

T-SQL Datetime functions

The first day of the week page 2