oracle 什么是 to-char 等效的 tsql/sql server 2008
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3494567/
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
what is the to-char equivalent tsql/sql server 2008
提问by icecurtain
I am used to oracle and now been thrown T-SQL, I am doing a course shortly but can you help out until then.
我已经习惯了 oracle，现在被抛出了 T-SQL，我正在做一个课程，但在那之前你能帮忙吗？
I have a list that I need to group in minutes.
我有一个需要在几分钟内分组的列表。
Sample of rowdate data
rowdate 数据示例
SELECT ROWDATE,count(rowdate)
FROM [mydb].[dbo].[mytable]
GROUP BY ROWDATE
order by 1
2010-08-16 15:01:18.110 1
2010-08-16 15:01:18.203 1
2010-08-16 15:01:18.377 1
2010-08-16 15:01:18.453 1
2010-08-16 15:01:18.530 1
2010-08-16 15:01:18.610 1
2010-08-16 15:01:18.703 1
2010-08-16 15:01:18.813 1
2010-08-16 15:01:18.953 1
2010-08-16 15:01:19.173 1
2010-08-16 15:01:19.360 1
2010-08-16 15:01:19.483 1
2010-08-16 15:01:19.593 1
2010-08-16 15:01:19.673 1
2010-08-16 15:01:19.733 1
2010-08-16 15:01:19.813 1
2010-08-16 15:01:19.890 1
2010-08-16 15:01:19.970 1
2010-08-16 15:01:20.047 1
I just want to group by mins.
我只想按分钟分组。
SELECT to_char(rowdate,'dd/MM/yyyy HH24:MI'),count(rowdate)
FROM mytable
GROUP BY to_char(rowdate,'dd/MM/yyyy HH24:MI')
order by 1
On sql server(T-SQL) what would be the equivalent script?
在 sql server(T-SQL) 上，等效的脚本是什么？
回答by OMG Ponies
The TSQL equivalent of:
TSQL 等效于：
TO_CHAR(rowdate,'dd/MM/yyyy HH24:MI')
...is:
...是：
CONVERT(VARCHAR(16), rowdate, 20)
The expression "20" returns: yyyy-mm-dd hh:mi:ss(24h)- which is VARCHAR(19), so cutting that down to VARCHAR(16) omits the seconds.
表达式 "20" 返回：yyyy-mm-dd hh:mi:ss(24h)- 这是 VARCHAR(19)，因此将其缩减为 VARCHAR(16) 会忽略秒。
DATETIME formatting is a real pain in TSQL - with SQL Server 2005, the only real way to get customizable formatting is to use a CLR function so in C# you could use the DateTime.ToStringmethod...
DATETIME 格式在 TSQL 中是一个真正的痛苦 - 对于 SQL Server 2005，获得可自定义格式的唯一真正方法是使用 CLR 函数，因此在 C# 中您可以使用该DateTime.ToString方法......
Reference:
参考：
回答by GSerg
That'd be
那会是
convert(varchar, rowdate, 100)
The format is not exactly the same, but the important thing, it's up to a minute, too.
格式不完全相同，但重要的是，它也最多一分钟。
EDIT:
编辑：
Alternatively, you can avoid conversion to varchar at all:
或者，您可以完全避免转换为 varchar：
group by dateadd(ms, -datepart(ms, rowdate) , dateadd(s, -datepart(s, rowdate), rowdate))
回答by KM.
to floor the datetime down to the minute use (which is better than using string manipulations):
将日期时间降低到分钟使用（这比使用字符串操作更好）：
DATEADD(minute,DATEDIFF(minute,0,datetime),0)
so, to group by minutes, it would be:
因此，按分钟分组，它将是：
SELECT
DATEADD(minute,DATEDIFF(minute,0,ROWDATE),0)
,count(rowdate)
FROM [mydb].[dbo].[mytable]
GROUP BY DATEADD(minute,DATEDIFF(minute,0,ROWDATE),0)
order by 1
回答by Rob
Is there a specific reason you want a conversion to (var)char?
您是否有特定原因想要转换为 (var)char？
datepart(minute, rowdate)
This returns just one number: the minute part of the datetime field. Very easy to group by.
这仅返回一个数字：日期时间字段的分钟部分。非常容易分组。
Otherwise use CONVERT() or CAST() to convert between character types. You'll also want to make sure you use the right format. There's a little bit more about that here: http://www.mssqltips.com/tip.asp?tip=1145
否则使用 CONVERT() 或 CAST() 在字符类型之间进行转换。您还需要确保使用正确的格式。这里有更多内容：http: //www.mssqltips.com/tip.asp?tip=1145
