如何从 SQL 中的 DATETIME 列获取 DATE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21521241/
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
How to get DATE from DATETIME Column in SQL?
提问by mvp
I have 3 columns in Table TransactionMaster in sql server
我在 sql server 的 Table TransactionMaster 中有 3 列
1) transaction_amount
1) 交易金额
2) Card_No
2) Card_No
3) transaction_date-- datetimedatatype
3）transaction_date--datetime数据类型
So, I want to fetch SUM of transaction_amount where Card_No=' 123'and transaction_date= todays date.<----- excluding time IN SQL
所以，我想获取transaction_amount where Card_No=' 123'和的SUM transaction_date= todays date.<----- 不包括 SQL 中的时间
回答by mvp
Simply cast your timestamp AS DATE, like this:
只需投射您的时间戳AS DATE，如下所示：
SELECT CAST(tstamp AS DATE)
In other words, your statement would look like this:
换句话说，您的语句将如下所示：
SELECT SUM(transaction_amount)
FROM mytable
WHERE Card_No='123'
AND CAST(transaction_date AS DATE) = target_date
What is nice about CASTis that it works exactly the same on most SQL engines (SQL Server, PostgreSQL, MySQL), and is much easier to remember how to use it.
Methods using CONVERT()or TO_DATE()are specific to each SQL engine and make your code non-portable.
它的优点CAST在于它在大多数 SQL 引擎（SQL Server、PostgreSQL、MySQL）上的工作方式完全相同，并且更容易记住如何使用它。使用CONVERT()或TO_DATE()特定于每个 SQL 引擎的方法并使您的代码不可移植。
回答by Milan Raval
You can use
您可以使用
select *
from transaction
where (Card_No='123') and (transaction_date = convert(varchar(10),getdate(),101))
回答by Developerzzz
use the following
使用以下
select sum(transaction_amount) from TransactionMaste
where Card_No = '123' and transaction_date = CONVERT(VARCHAR(10),GETDATE(),111)
or the following
或以下
select sum(transaction_amount) from TransactionMaste
where Card_No = '123' and transaction_date = CONVERT(VARCHAR(10), GETDATE(), 120)
回答by Nagaraj S
Use Getdate()
用 Getdate()
select sum(transaction_amount) from TransactionMaster
where Card_No=' 123' and transaction_date =convert(varchar(10), getdate(), 102)
回答by Ajay
Try this:
尝试这个：
SELECT SUM(transaction_amount) FROM TransactionMaster WHERE Card_No ='123' AND CONVERT(VARCHAR(10),GETDATE(),111)
The GETDATE()function returns the current date and timefrom the SQL Server.
该GETDATE()函数从 SQL Server返回当前日期和时间。
