将一天的名称转换为其整数表示

2021-09-11 00:00:00 sql type-conversion sql-server

在 SQL Server 中,您可以使用 DATENAME 函数以字符串形式获取星期几

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

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

返回星期二"

并且您可以使用 DATEPART 函数以整数形式获取星期几

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

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

返回 3

但是假设我有一个包含字符串Tuesday"的 varchar,我想将它转换为 3 的整数表示.当然,我可以毫不费力地写出转换,但我更愿意使用内置的- 在功能.有这样的功能吗?

But say I have a varchar that contains the string "Tuesday" and I want to convert it to its integer representation of 3. Sure, I could write out the conversion without much hassle, but I'd much rather use a built-in function. Does such a function exist?

推荐答案

与其编写函数,不如创建一个包含说明和数值的星期几表.然后您可以简单地加入表格以获取数字.

Rather than write a function, you should create a days of the week table with the description and the numeric value. THen you can simply join to the table to get the numeric.

如果您以多种方式存储天数(可能在基于字符的系统中),您可以将所有变体放入表中,因此 TUE、Tues.、Tuesday 都将映射到相同的整数.

And if you have days stored multiple ways (likely in a characterbased system), you can put all the variants into the table, so TUE, Tues., Tuesday would all map to the same integer.

相关文章