SQL 如何从日期时间中删除时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14279849/
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 remove time from datetime
提问by user1860212
The field DATE in the database has the following format:
数据库中的字段 DATE 具有以下格式：
2012-11-12 00:00:00
I would like to remove the time from the date and return the date like this:
我想从日期中删除时间并返回这样的日期：
11/12/2012
回答by GarethD
First thing's first, if your dates are in varchar format change that, store dates as datesit will save you a lot of headaches and it is something that is best done sooner rather than later. The problem will only get worse.
首先，如果您的日期是 varchar 格式的更改，将日期存储为日期会为您省去很多麻烦，而且最好尽早完成。问题只会变得更糟。
Secondly, once you have a date DO NOTconvert the date to a varchar! Keep it in date format and use formatting on the application side to get the required date format.
其次，一旦您有了日期，请勿将日期转换为 varchar！保持日期格式并在应用程序端使用格式来获取所需的日期格式。
There are various methods to do this depending on your DBMS:
根据您的 DBMS，有多种方法可以执行此操作：
SQL-Server 2008and later:
SQL-Server 2008及更高版本：
SELECT CAST(CURRENT_TIMESTAMP AS DATE)
SQL-Server 2005and Earlier
SQL-Server 2005及更早版本
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
SQLite
SQLite
SELECT DATE(NOW())
Oracle
甲骨文
SELECT TRUNC(CURRENT_TIMESTAMP)
Postgresql
PostgreSQL
SELECT CURRENT_TIMESTAMP::DATE
If you need to use culture specific formatting in your report you can either explicitly state the format of the receiving text box (e.g. dd/MM/yyyy), or you can set the language so that it shows the relevant date format for that language.
如果您需要在报告中使用特定于文化的格式，您可以明确说明接收文本框的格式（例如 dd/MM/yyyy），或者您可以设置语言以显示该语言的相关日期格式。
Either way this is much better handled outside of SQL as converting to varchar within SQL will impact any sorting you may do in your report.
无论哪种方式，这都可以在 SQL 之外更好地处理，因为在 SQL 中转换为 varchar 会影响您在报告中可能进行的任何排序。
If you cannot/will not change the datatype to DATETIME, then still convert it to a date within SQL (e.g. CONVERT(DATETIME, yourField)) before sending to report services and handle it as described above.
如果您不能/不会将数据类型更改为 DATETIME，那么CONVERT(DATETIME, yourField)在发送到报告服务之前仍将其转换为 SQL 中的日期（例如）并按上述方式处理它。
回答by John Woo
just use, (in TSQL)
只需使用，（在 TSQL 中）
SELECT convert(varchar, columnName, 101)
in MySQL
在MySQL 中
SELECT DATE_FORMAT(columnName, '%m/%d/%Y')
回答by SnellyCat
I found this method to be quite useful. However it will convert your date/time format to just date but never the less it does the job for what I need it for. (I just needed to display the date on a report, the time was irrelevant).
我发现这个方法非常有用。但是，它会将您的日期/时间格式转换为日期，但它可以满足我的需要。（我只需要在报告上显示日期，时间无关紧要）。
CAST(start_date AS DATE)
UPDATE
更新
(Bear in mind I'm a trainee ;))
（请记住，我是一名实习生；））
I figured an easier way to do this IF YOU'RE USING SSRS.
如果您使用 SSRS，我想出了一种更简单的方法来做到这一点。
It's easier to actually change the textbox properties where the field is located in the report. Right click field>Number>Date and select the appropriate format!
实际更改报表中字段所在位置的文本框属性会更容易。右键单击字段>数字>日期并选择适当的格式！
回答by gbn
Personally, I'd return the full, native datetime value and format this in the client code.
就个人而言，我会返回完整的本地日期时间值并在客户端代码中对其进行格式化。
That way, you can use the user's locale setting to give the correct meaning to that user.
这样，您就可以使用用户的区域设置为该用户赋予正确的含义。
"11/12" is ambiguous. Is it:
“11/12”含糊不清。是吗：
- 12th November
- 11th December
- 11 月 12 日
- 12 月 11 日
回答by Davide Berra
SELECT DATE('2012-11-12 00:00:00');
returns
回报
2012-11-12
回答by Vishal Suthar
For more info refer this: SQL Server Date Formats
有关更多信息，请参阅：SQL Server 日期格式
[MM/DD/YYYY]
[月/日/年]
SELECT CONVERT(VARCHAR(10), cast(dt_col as date), 101) from tbl
[DD/MM/YYYY]
[日/月/年]
SELECT CONVERT(VARCHAR(10), cast(dt_col as date), 103) from tbl
Live Demo
现场演示
回答by bonCodigo
You may try the following:
您可以尝试以下操作：
SELECT CONVERT(VARCHAR(10),yourdate,101);
or this:
或这个：
select cast(floor(cast(urdate as float)) as datetime);
回答by Bud Damyanov
Use this SQL:
使用此 SQL：
SELECT DATE_FORMAT(date_column_here,'%d/%m/%Y') FROM table_name;
回答by uzay95
TSQL
TSQL
SELECT CONVERT(DATE, GETDATE()) // 2019-09-19
SELECT CAST(GETDATE() AS DATE) // 2019-09-19
SELECT CONVERT(VARCHAR, GETDATE(), 23) // 2019-09-19
回答by Bart Friederichs
In mysql at least, you can use DATE(theDate).
至少在 mysql 中，您可以使用DATE(theDate).
