SQL Server 中的自定义日期/时间格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/202243/
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
Custom Date/Time formatting in SQL Server
提问by Pascal
I am trying to write a stored procedure which selects columns from a table and adds 2 extra columns to the ResultSet. These 2 extra columns are the result of conversions on a field in the table which is a Datetime field.
我正在尝试编写一个存储过程，该过程从表中选择列并将 2 个额外的列添加到 ResultSet。这 2 个额外的列是表中一个字段的转换结果，该字段是一个日期时间字段。
The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'
日期时间格式字段具有以下格式 'YYYY-MM-DD HH:MM:SS.S'
The 2 additional fields which should be in the following format:
应采用以下格式的 2 个附加字段：
- DDMMM
- HHMMT, where T is 'A' for a.m. and 'P' for p.m.
- DDMMM
- HHMMT，其中 T 是“A”代表上午，“P”代表下午
Example: If the data in the field was '2008-10-12 13:19:12.0' then the extracted fields should contain:
示例：如果字段中的数据为“2008-10-12 13:19:12.0”，则提取的字段应包含：
- 12OCT
- 0119P
- 12OCT
- 0119P
I have tried using CONVERT string formats, but none of the formats match the output I want to get. I am thinking along the lines of extracting the field data via CONVERT and then using REPLACE, but I surely need some help here, as I am no sure.
我曾尝试使用 CONVERT 字符串格式，但没有一种格式与我想要的输出相匹配。我正在考虑通过 CONVERT 提取字段数据，然后使用 REPLACE，但我在这里肯定需要一些帮助，因为我不确定。
Could anyone well versed in stored procedures help me out here? Thanks!
任何精通存储过程的人都可以帮助我吗？谢谢！
采纳答案by Cade Roux
If dt is your datetime column, then
如果 dt 是您的日期时间列，则
For 1:
对于 1：
SUBSTRING(CONVERT(varchar, dt, 13), 1, 2)
+ UPPER(SUBSTRING(CONVERT(varchar, dt, 13), 4, 3))
For 2:
对于 2：
SUBSTRING(CONVERT(varchar, dt, 100), 13, 2)
+ SUBSTRING(CONVERT(varchar, dt, 100), 16, 3)
回答by StingyHyman
Use DATENAME and wrap the logic in a Function, not a Stored Proc
使用 DATENAME 并将逻辑包装在函数中，而不是存储过程中
declare @myTime as DateTime
set @myTime = GETDATE()
select @myTime
select DATENAME(day, @myTime) + SUBSTRING(UPPER(DATENAME(month, @myTime)), 0,4)
Returns "14OCT"
返回“14OCT”
Try not to use any Character / String based operations if possible when working with dates. They are numerical (a float) and performance will suffer from those data type conversions.
如果可能，在处理日期时尽量不要使用任何基于字符/字符串的操作。它们是数字（浮点数），性能会受到这些数据类型转换的影响。
Dig these handy conversions I have compiled over the years...
挖掘我多年来编译的这些方便的转换......
/* Common date functions */
--//This contains common date functions for MSSQL server
/*Getting Parts of a DateTime*/
--//gets the date only, 20x faster than using Convert/Cast to varchar
--//this has been especially useful for JOINS
SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))
--//gets the time only (date portion is '1900-01-01' and is considered the "0 time" of dates in MSSQL, even with the datatype min value of 01/01/1753.
SELECT (GETDATE() - (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)))
/*Relative Dates*/
--//These are all functions that will calculate a date relative to the current date and time
/*Current Day*/
--//now
SELECT (GETDATE())
--//midnight of today
SELECT (DATEADD(ms,-4,(DATEADD(dd,DATEDIFF(dd,0,GETDATE()) + 1,0))))
--//Current Hour
SELECT DATEADD(hh,DATEPART(hh,GETDATE()),CAST(FLOOR(CAST(GETDATE() AS FLOAT)) as DateTime))
--//Current Half-Hour - if its 9:36, this will show 9:30
SELECT DATEADD(mi,((DATEDIFF(mi,(CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)), GETDATE())) / 30) * 30,(CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)))
/*Yearly*/
--//first datetime of the current year
SELECT (DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
--//last datetime of the current year
SELECT (DATEADD(ms,-4,(DATEADD(yy,DATEDIFF(yy,0,GETDATE()) + 1,0))))
/*Monthly*/
--//first datetime of current month
SELECT (DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
--//last datetime of the current month
SELECT (DATEADD(ms,-4,DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))))
--//first datetime of the previous month
SELECT (DATEADD(mm,DATEDIFF(mm,0,GETDATE()) -1,0))
--//last datetime of the previous month
SELECT (DATEADD(ms, -4,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))
/*Weekly*/
--//previous monday at 12AM
SELECT (DATEADD(wk,DATEDIFF(wk,0,GETDATE()) -1 ,0))
--//previous friday at 11:59:59 PM
SELECT (DATEADD(ms,-4,DATEADD(dd,5,DATEADD(wk,DATEDIFF(wk,0,GETDATE()) -1 ,0))))
/*Quarterly*/
--//first datetime of current quarter
SELECT (DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0))
--//last datetime of current quarter
SELECT (DATEADD(ms,-4,DATEADD(qq,DATEDIFF(qq,0,GETDATE()) + 1,0)))
回答by Mehdi
You can use the following command in SQL server to make it:
您可以在 SQL Server 中使用以下命令来制作它：
select FORMAT(getdate(), N'yyyy-MM-ddThh:mm:ss')
回答by Kibbee
Not answering your question specifically, but isn't that something that should be handled by the presentation layer of your application. Doing it the way you describe creates extra processing on the database end as well as adding extra network traffic (assuming the database exists on a different machine than the application), for something that could be easily computed on the application side, with more rich date processing libraries, as well as being more language agnostic, especially in the case of your first example which contains the abbreviated month name. Anyway the answers others give you should point you in the right direction if you still decide to go this route.
没有具体回答您的问题，但这不应该由您的应用程序的表示层处理。按照您描述的方式进行操作会在数据库端创建额外的处理并添加额外的网络流量（假设数据库存在于与应用程序不同的机器上），对于可以在应用程序端轻松计算的内容，具有更丰富的数据处理库，以及更多的语言不可知，特别是在你的第一个例子中，它包含缩写的月份名称。无论如何，如果您仍然决定走这条路，其他人给您的答案应该为您指明正确的方向。
回答by Joel Coehoorn
The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'
日期时间格式字段具有以下格式 'YYYY-MM-DD HH:MM:SS.S'
That statement is false. That's just how Enterprise Manager or SQL Server chooses to showthe date. Internally it's a 8-byte binary value, which is why some of the functions posted by Andrew will work so well.
这种说法是错误的。这就是企业管理器或 SQL Server 选择显示日期的方式。在内部，它是一个 8 字节的二进制值，这就是为什么 Andrew 发布的某些函数会如此有效的原因。
Kibbee makes a valid point as well, and in a perfect world I would agree with him. However, sometimes you want to bind query results directly to display control or widgets and there's really not a chance to do any formatting. And sometimes the presentation layer lives on a web server that's even busier than the database. With those in mind, it's not necessarily a bad thing to know how to do this in SQL.
Kibbee 也提出了一个有效的观点，在一个完美的世界中，我会同意他的观点。但是，有时您希望将查询结果直接绑定到显示控件或小部件，而实际上没有机会进行任何格式化。有时表示层位于比数据库更繁忙的 Web 服务器上。考虑到这些，知道如何在 SQL 中执行此操作不一定是件坏事。
回答by Davut Gürbüz
Yes Depart is a solution for that but I think this kind of methods are long trips!
Yes Depart 是一个解决方案，但我认为这种方法是长途旅行！
SQL SERVER:
SQL 服务器：
SELECT CAST(DATEPART(DD,GETDATE()) AS VARCHAR)+'/'
+CAST(DATEPART(MM,GETDATE()) AS VARCHAR)
+'/'+CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR)
+' '+CAST(DATEPART(HH,GETDATE()) AS VARCHAR)
+':'+CAST(DATEPART(MI,GETDATE()) AS VARCHAR)
Oracle:
甲骨文：
Select to_char(sysdate,'DD/MM/YYYY HH24:MI') from dual
You may write your own function by this way you can get rid of this mess;
您可以通过这种方式编写自己的函数，您可以摆脱这种混乱；
http://sql.dzone.com/news/custom-date-formatting-sql-ser
http://sql.dzone.com/news/custom-date-formatting-sql-ser
select myshortfun(getdate(),myformat)
GO
回答by ch2o
in MS SQL Server you can do:
在 MS SQL Server 中，您可以执行以下操作：
SET DATEFORMAT ymd
设置日期格式 ymd
year, month, day,
年月日，
回答by Mark
If it's something more specific like DateKey(yyyymmdd) that you need for dimensional models, I suggest something without any casts/converts:
如果它是维度模型所需的更具体的东西，例如DateKey( yyyymmdd) ，我建议一些没有任何强制转换/转换的东西：
DECLARE @DateKeyToday int = (SELECT 10000 * DATEPART(yy,GETDATE()) + 100 * DATEPART(mm,GETDATE()) + DATEPART(dd,GETDATE()));
PRINT @DateKeyToday
回答by Pawel Cioch
I'm adding this answer (for myself) as relevant to custom formatting.
我正在添加这个与自定义格式相关的答案（为我自己）。
For underscore yyyy_MM_dd
对于下划线 yyyy_MM_dd
REPLACE(SUBSTRING(CONVERT(VARCHAR, @dt, 120), 1, 10),'-','_')
回答by Paul Williams
You're going to need DATEPART here. You can concatenate the results of the DATEPART calls together.
您将需要 DATEPART 在这里。您可以将 DATEPART 调用的结果连接在一起。
To get the month abbreviations, you might be able to use DATENAME; if that doesn't work for you, you can use a CASE statement on the DATEPART.
要获取月份缩写，您可以使用 DATENAME；如果这对您不起作用，您可以在 DATEPART 上使用 CASE 语句。
DATEPART also works for the time field.
DATEPART 也适用于时间字段。
I can think of a couple of ways of getting the AM/PM indicator, including comparing new dates built via DATEPART or calculating the total seconds elapsed in the day and comparing that to known AM/PM thresholds.
我可以想到几种获取 AM/PM 指标的方法，包括比较通过 DATEPART 构建的新日期或计算当天经过的总秒数并将其与已知的 AM/PM 阈值进行比较。
