Translate

sábado, 25 de agosto de 2012

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

Um comentário:

Anônimo disse...

I was pretty pleased to discover this website.
I want to to thank yoou for your time just for this fantastic read!!

I definitely liked ever bit of itt and i also have
you szved as a favorite to check out new stuff inn your weeb
site.

Feeel free to vvisit myy site ... Facebook Stock Expected To Rise

Postar um comentário

Deixe seu comentário aqui.