I have the following query
SELECT DATEPART(WEEKDAY,'2018-01-18')
What I want is an example that if that day is Thursday, I return a 3 because in the table that I have the days start from 0 to 6 the field is of type int and is called DAYID , by when I use the aforementioned query returned me the number 5
If what you want is a function (I do not know whether to recommend you) that returns the day of the week, without considering the previous answer (which is valid). You should do, for example:
CREATE FUNCTION UDF_DIASEMANA(
@FECHA DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @DIA INT = DATEPART(WEEKDAY, @FECHA)
RETURN @DIA + CASE WHEN @DIA >= 2 THEN -2 ELSE 5 END
END
GO
SELECT
DBO.UDF_DIASEMANA('20180115'),
DBO.UDF_DIASEMANA('20180116'),
DBO.UDF_DIASEMANA('20180117'),
DBO.UDF_DIASEMANA('20180118'),
DBO.UDF_DIASEMANA('20180119'),
DBO.UDF_DIASEMANA('20180120'),
DBO.UDF_DIASEMANA('20180121'),
DBO.UDF_DIASEMANA('20180122')
You'll get:
+---+---+---+---+---+---+---+---+ | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 0 | +---+---+---+---+---+---+---+---+
Sql server lists the days of the week from 1 to 7 starting with Sunday as day 1, Saturday day 7, to achieve what you need use:
--Indicamos al motor que el lunes lo considere como el primer dia de la semana
SET DATEFIRST 1
--Al enumerar los dias de la semana del 1 al 7 necesitamos
--restar 1 a la enumeración para que concuerde con la tabla que tienes
SELECT (DATEPART(WEEKDAY,'2018-01-18') - 1) -- resultado 3
SELECT (DATEPART(WEEKDAY,'2018-01-15') - 1) -- resultado 0