Dias da Semana entre SQL Server e Firebird

A função que retorna o dia da semana no SQL Server e Firebird tem resultados diferentes.

Firebird:

SELECT
  EXTRACT(WEEKDAY FROM CAST('19.08.2012' AS DATE)) AS Domingo,
  EXTRACT(WEEKDAY FROM CAST('20.08.2012' AS DATE)) AS Segunda,
  EXTRACT(WEEKDAY FROM CAST('21.08.2012' AS DATE)) AS Terca,
  EXTRACT(WEEKDAY FROM CAST('22.08.2012' AS DATE)) AS Quarta,
  EXTRACT(WEEKDAY FROM CAST('23.08.2012' AS DATE)) AS Quinta,
  EXTRACT(WEEKDAY FROM CAST('24.08.2012' AS DATE)) AS Sexta,
  EXTRACT(WEEKDAY FROM CAST('25.08.2012' AS DATE)) AS Sabado
FROM RDB$DATABASE

O resultado para o Firebird foi:
0 - Domingo; 1 - Segunda; 2 - Terça; 3 - Quarta; 4 - Quinta; 5 - Sexta; 6 - Sábado;

SQL Server: Sem alterar a configuração do DATEFIRST.

USE NOME_BANCO
GO
SELECT
  @@DATEFIRST
SELECT 
  DATEPART(WEEKDAY, '2012-08-19') AS Domingo,
  DATEPART(WEEKDAY, '2012-08-20') AS Segunda,
  DATEPART(WEEKDAY, '2012-08-21') AS Terça,
  DATEPART(WEEKDAY, '2012-08-22') AS Quarta,
  DATEPART(WEEKDAY, '2012-08-23') AS Quinta,
  DATEPART(WEEKDAY, '2012-08-24') AS Sexta,
  DATEPART(WEEKDAY, '2012-08-25') AS Sábado

O resultado para o SQL Server foi:
1 - Domingo; 2 - Segunda; 3 - Terça; 4 - Quarta; 5 - Quinta; 6 - Sexta; 7 - Sábado;

SQL Server: Alterando a configuração do DATEFIRST para 4.

USE NOME_BANCO
GO
SET DATEFIRST = 4
SELECT 
  DATEPART(WEEKDAY, '2012-08-19') AS Domingo,
  DATEPART(WEEKDAY, '2012-08-20') AS Segunda,
  DATEPART(WEEKDAY, '2012-08-21') AS Terça,
  DATEPART(WEEKDAY, '2012-08-22') AS Quarta,
  DATEPART(WEEKDAY, '2012-08-23') AS Quinta,
  DATEPART(WEEKDAY, '2012-08-24') AS Sexta,
  DATEPART(WEEKDAY, '2012-08-25') AS Sábado

O resultado para o SQL Server foi diferente do anterior:
4 - Domingo; 5 - Segunda; 6 - Terça; 7 - Quarta; 1 - Quinta; 2 - Sexta; 3 - Sábado;

SQL Server: Solução ideal quando você não quer que o DATEFIRST influencie nos resultados.

USE NOME_BANCO
GO
SET DATEFIRST = 7 -- Padrão
SELECT 
  (DATEPART(DW, '2012-08-19') + @@DATEFIRST) % 7 as Domingo,
  (DATEPART(DW, '2012-08-20') + @@DATEFIRST) % 7 as Segunda,
  (DATEPART(DW, '2012-08-21') + @@DATEFIRST) % 7 as Terça,
  (DATEPART(DW, '2012-08-22') + @@DATEFIRST) % 7 as Quarta,
  (DATEPART(DW, '2012-08-23') + @@DATEFIRST) % 7 as Quinta,
  (DATEPART(DW, '2012-08-24') + @@DATEFIRST) % 7 as Sexta,
  (DATEPART(DW, '2012-08-25') + @@DATEFIRST) % 7 as Sábado

SET DATEFIRST = 4
SELECT 
  (DATEPART(DW, '2012-08-19') + @@DATEFIRST) % 7 as Domingo,
  (DATEPART(DW, '2012-08-20') + @@DATEFIRST) % 7 as Segunda,
  (DATEPART(DW, '2012-08-21') + @@DATEFIRST) % 7 as Terça,
  (DATEPART(DW, '2012-08-22') + @@DATEFIRST) % 7 as Quarta,
  (DATEPART(DW, '2012-08-23') + @@DATEFIRST) % 7 as Quinta,
  (DATEPART(DW, '2012-08-24') + @@DATEFIRST) % 7 as Sexta,
  (DATEPART(DW, '2012-08-25') + @@DATEFIRST) % 7 as Sábado

O resultado para o SQL Server foi:
1 - Domingo; 2 - Segunda; 3 - Terça; 4 - Quarta; 5 - Quinta; 6 - Sexta; 0 - Sábado;
1 - Domingo; 2 - Segunda; 3 - Terça; 4 - Quarta; 5 - Quinta; 6 - Sexta; 0 - Sábado;

SQL Server: Para ter o mesmo resultado do Firebird.

USE NOME_BANCO
GO
SELECT 
  (DATEPART(DW, '2012-08-19') + @@DATEFIRST - 1) % 7 as Domingo,
  (DATEPART(DW, '2012-08-20') + @@DATEFIRST - 1) % 7 as Segunda,
  (DATEPART(DW, '2012-08-21') + @@DATEFIRST - 1) % 7 as Terça,
  (DATEPART(DW, '2012-08-22') + @@DATEFIRST - 1) % 7 as Quarta,
  (DATEPART(DW, '2012-08-23') + @@DATEFIRST - 1) % 7 as Quinta,
  (DATEPART(DW, '2012-08-24') + @@DATEFIRST - 1) % 7 as Sexta,
  (DATEPART(DW, '2012-08-25') + @@DATEFIRST - 1) % 7 as Sábado

O resultado para o SQL Server foi:
0 - Domingo; 1 - Segunda; 2 - Terça; 3 - Quarta; 4 - Quinta; 5 - Sexta; 6 - Sábado;
O resultado para o Firebird foi:
0 - Domingo; 1 - Segunda; 2 - Terça; 3 - Quarta; 4 - Quinta; 5 - Sexta; 6 - Sábado;

Muito cuidado para quem está migrando de banco, espero que você tenha testes para isso :)

Fonte:
- http://blog.sqlauthority.com/2007/04/22/sql-server-datefirst-and-set-datefirst-relations-and-usage
- www.kodyaz.com/articles/get-week-day-name-of-date-using-t-sql.aspx