SQL 投射功能以仅显示日期时间中的月份和日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28390096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cast Function to display only the month and day from a datetime
提问by Zac Davidson
SELECT
DateAdded,
Cast(DateAdded AS Date) AS DateAddedV1,
Cast(DateAdded AS Time) AS DateTime,
SELECT CAST(DateAdded AS Date(mm:dd) AS OrderDate
FROM Products
I am a beginner with SQL and I am trying to return the DateAddedcolumn as OrderDatewith only the month and the day but I cannot seem to get the syntax right. I appreciate anyone's assistance.
我是 SQL 的初学者，我试图只返回月份和日期的DateAdded列，OrderDate但我似乎无法获得正确的语法。我感谢任何人的帮助。
回答by P?????
Use DatePartFunction to extract dayand monthfrom Datetimetype,
使用DatePart函数来提取day和month从Datetime类型，
Select Datepart(Month,DateAdded) AS [Month], -- Month(Dateadded)
Datepart(Day,DateAdded) as [Day], -- Day(Dateadded)
..
From Products
Update:Only by using castfunction you cannot extract Monthand Day. If you want to keep month and day in same column
更新：只有使用cast函数你不能提取Month和Day。如果您想将月份和日期保留在同一列中
SELECT CONVERT(VARCHAR(2), Month(DateAdded)) + ':'
+ CONVERT(VARCHAR(2), Day(DateAdded))
.....
To get leading zero's use rightfunction and to extract Timefrom DateAdded use Convertfunction with 108value
获取前导零的使用right函数并Time从Convert带108值的DateAdded 使用函数中提取
SELECT right('0'+CONVERT(VARCHAR(2), Month(DateAdded)),2) + ':'
+ right('0'+CONVERT(VARCHAR(2), Day(DateAdded)) ,2) as [Mon:Day],
convert(varchar(10),DateAdded,108) as [Time]
.........
回答by Mega Lopunny
I think I'm working on this assignment this semester. This is what I ended up with since it HAD to be done using the CAST function:
我想我这个学期正在做这个作业。这就是我最终得到的结果，因为它必须使用 CAST 函数来完成：
SELECT DateAdded,
CAST(DateAdded AS date) AS DateOnly,
CAST(DateAdded AS time) AS TimeOnly,
CAST(DateAdded AS varchar(6)) MonthDay
FROM Products;
This works because varchar always has the Month the Day exactly in the first 6 characters. All the Months are 3 Characters (Jan, Feb, Mar, Apr) and then there is either a space then a 2-digit day, or 2 spaces and a 1-digit day. Either way it always adds up to 6 characters.
这是有效的，因为 varchar 总是在前 6 个字符中准确地显示月份日期。所有月份都是 3 个字符（一月、二月、三月、四月），然后有一个空格然后是 2 位数字的日期，或 2 个空格和 1 位数字的日期。无论哪种方式，它总是加起来最多 6 个字符。
回答by Used_By_Already
Here you are asking about the visible format of date/time information.
在这里，您询问日期/时间信息的可见格式。
- In MS SQL Server from SQ 2012 and later you can use a FORMAT()function. If you use a version that supports this function I recommend you use it. Keep in mind that the second parameter is CASE SENSITIVE
- In all versions you may use the CONVERT()function, which has this syntax:
- 在 SQ 2012 及更高版本的 MS SQL Server 中，您可以使用FORMAT()函数。如果您使用支持此功能的版本，我建议您使用它。请记住，第二个参数是 CASE SENSITIVE
- 在所有版本中，您都可以使用CONVERT()函数，其语法如下：
CONVERT ( data_type [ ( length ) ] , expression [ , style] )
CONVERT（数据类型[（长度）]，表达式[，样式]）
Note in paticular that styleparameter (an integer) this is important if using CONVERT. A very useful reference for using either approach for display of date/time information is HEREand below is a tiny example:
请特别注意，如果使用 CONVERT ，样式参数（整数）这很重要。使用任一方法显示日期/时间信息的一个非常有用的参考是这里，下面是一个小例子：
Query 1:
查询 1：
SELECT
FORMAT(SYSDATETIME(), 'MMM dd yyyy')
, CONVERT(VARCHAR(11), SYSDATETIME(), 100) -- note length set to (11)
结果：
|-------------|-------------|
| Oct 11 2015 | Oct 11 2015 |
回答by Erik-Bart Rosman
Instead of converting, as in NoDisPlayName's answer, you could use the RTRIMfunctionality.
您可以使用该RTRIM功能，而不是像 NoDisPlayName 的回答那样进行转换。
SELECT RIGHT('0' + RTRIM(MONTH(DateAdded)), 2)
SELECT RIGHT('0' + RTRIM(MONTH(DateAdded)), 2)
