如何从日期时间中删除时间 [英] how to remove time from datetime
问题描述
数据库中的字段DATE格式如下:
The field DATE in the database has the following format:
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
推荐答案
首先，如果您的日期是 varchar 格式，请更改它，将日期存储为日期 它将为您省去很多麻烦，而且最好尽早完成比以后.问题只会变得更糟.
First thing's first, if your dates are in varchar format change that, store dates as dates it 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 NOT convert the date to a varchar! Keep it in date format and use formatting on the application side to get the required date format.
有多种方法可以执行此操作，具体取决于您的 DBMS:
There are various methods to do this depending on your DBMS:
SQL-Server 2008 及更高版本:
SELECT CAST(CURRENT_TIMESTAMP AS DATE)
<小时>
SQL-Server 2005 及更早版本
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
<小时>
SQLite
SELECT DATE(NOW())
<小时>
甲骨文
SELECT TRUNC(CURRENT_TIMESTAMP)
<小时>
Postgresql
SELECT CURRENT_TIMESTAMP::DATE
<小时>
如果您需要在报告中使用特定于文化的格式，您可以明确说明接收文本框的格式(例如 dd/MM/yyyy)，或者您可以设置语言以显示相关日期格式那种语言.
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.
无论哪种方式都可以在 SQL 之外更好地处理这种情况，因为在 SQL 中转换为 varchar 会影响您在报告中可能进行的任何排序.
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.
如果您不能/不会将数据类型更改为 DATETIME，则在发送到报告服务之前仍将其转换为 SQL 中的日期(例如 CONVERT(DATETIME, yourField))并按照描述进行处理
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.
这篇关于如何从日期时间中删除时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
