Geek Answers Handbook

Converting the name of the day to its whole representation

In SQL server, you can use the DATENAME function to get the day of the week as a string

declare @date datetime
set @date = '12/16/08'
select datename(dw, @date)

which returns tuesday

and you can use the DATEPART function to get the day of the week as an integer

declare @date datetime
set @date = '12/16/08'
select datepart(dw, @date)

What returns 3

But I will say that I have a varchar that contains the string "Tuesday", and I want to convert it to the whole representation 3. Of course, I could write the conversion without much hassle, but I would rather use the built-in function. Is there such a function?

+5
source share
9 answers

, , . , .

, (, ), , TUE, ., .

+10

, , :


CREATE FUNCTION dbo.WeekDay(@DayOfWeek Varchar(9))
RETURNS INT
            AS
    BEGIN
    DECLARE @iDayofWeek INT
    SELECT @iDayofWeek = CASE @DayOfWeek
                    WHEN 'Sunday' THEN 1
                    WHEN 'Monday' THEN 2
                    WHEN 'Tuesday' THEN 3
                    WHEN 'Wednesday' THEN 4
                    WHEN 'Thursday' THEN 5
                    WHEN 'Friday' THEN 6
                    WHEN 'Saturday' THEN 7
        END
    RETURN (@iDayofWeek)
    END
GO
+9

6 SQL Server, ( ). , sql-server-2014 - .

@RussBradberry, IF.

CREATE FUNCTION dbo.WeekDayInt (@DayOfWeekName VARCHAR(10))
RETURNS INT
AS
    BEGIN
        DECLARE @DayWeekNumber INT
        IF @DayOfWeekName = 'Sunday'    SET @DayWeekNumber = 1
        IF @DayOfWeekName = 'Monday'    SET @DayWeekNumber = 2
        IF @DayOfWeekName = 'Tuesday'   SET @DayWeekNumber = 3
        IF @DayOfWeekName = 'Wednesday' SET @DayWeekNumber = 4
        IF @DayOfWeekName = 'Thursday'  SET @DayWeekNumber = 5
        IF @DayOfWeekName = 'Friday'    SET @DayWeekNumber = 6
        IF @DayOfWeekName = 'Saturday'  SET @DayWeekNumber = 7;
        RETURN (@DayWeekNumber)
    END

, , , , .

CREATE FUNCTION dbo.WeekDayInt (@DayOfWeekName VARCHAR(10))
RETURNS INT
AS
    BEGIN
        IF @DayOfWeekName = 'Sunday'    RETURN 1
        IF @DayOfWeekName = 'Monday'    RETURN 2
        IF @DayOfWeekName = 'Tuesday'   RETURN 3
        IF @DayOfWeekName = 'Wednesday' RETURN 4
        IF @DayOfWeekName = 'Thursday'  RETURN 5
        IF @DayOfWeekName = 'Friday'    RETURN 6
        IF @DayOfWeekName = 'Saturday'  RETURN 7;
        RETURN 0;
    END
+1

, , .

    Select CHARINDEX(SUBSTRING('Thursday',1,3), 'MONTUEWEDTHUFRISATSUN') / 3 + 1

.

: SUBSTRING ( "", 1,3) Thu, charindex thu , 10. 10 , 3, 3, ,

1 2 3 , 4

1

4.

, - , , .

. , :

SELECT ID,DayNamesColumn from mytable ORDER BY CHARINDEX(SUBSTRING(DayNamesColumn ,1,3), 'MONTUEWEDTHUFRISATSUN') / 3 + 1
+1

, iso 2017-12-15 (, ) @@datefirst, . . datefirst = 1 .

select
  w.weekday_id_iso8601,
  w.weekday_id_datepart,
  w.weekday_name
from (
       values
         (7, 'Monday'),
         (8, 'Tuesday'),
         (9, 'Wednesday'),
         (10, 'Thursday'),
         (11, 'Friday'),
         (12, 'Saturday'),
         (13, 'Sunday'))
     t(weekday_id, weekday_name)
cross apply (
  select
    t.weekday_id - 6 weekday_id_iso8601,
    (t.weekday_id - @@datefirst + 1) % 7 + 1 weekday_id_datepart,
    t.weekday_name
) w
where w.weekday_id_datepart = datepart(weekday, '2017-12-15')
+1

, . .

; , case . , , , .

0

, , .:) .

, DATEPART DATENAME @@DATEFIRST . SET DATEFIRST .

DECLARE
    @date_name AS VARCHAR(20)

SET @date_name = 'Monday'

SELECT
    DATEPART(dw, my_date)
FROM
    (
    SELECT CAST('1900-01-01' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-02' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-03' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-04' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-05' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-06' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-07' AS DATETIME) AS my_date
    ) AS My_Dates
WHERE
    DATENAME(dw, my_date) = @date_name
0

DayID - 1 7.

DayID , 1899-12-31. ( - , 1899-12-30)

1900-01-01 , 1900-01-02 ..

:

select DayID, 
       datename( weekday, dateadd( day, DayID, '18991231' ) )
0

SQL 2014 :

SELECT DATEPART (DW, GetDate()) .

0

More articles:


All Articles