Oracle - 获取当前日期格式 [英] Oracle - get current date formatted

问题描述

我一直在想，我的系统中sysdate有很多用处，当与日期列进行比较时，我必须使用trunc（sysdate），因为sysdate的格式是 DD / MM / YYYY HH24：MI：SS 。

我浏览了互联网以获取其他功能，以返回格式为<$ c的当前日期$ c> DD / MM / YYYY 但没有运气， current_date，current_timestamp 和ETC也给了我小时格式..

当将索引日期列与当前日期进行比较时，我需要这样才能获得更好的性能。

所以，任何人都知道系统函数返回所需的格式？或者绕过它的方法？

解决方案

如果您的date列类型为DATE，那么您不需要关注格式。您需要将trunc（column_name）与trunc（sysdate）进行比较。而且如果导致严重的性能问题，因为使用某个功能会消除索引的使用，然后创建一个基于函数的索引。一个不太优雅的解决方案将是

WHERE mydatecol> trunc（sysdate）
AND mydatecol< trunc（sysdate +1）

您不想做的是将DATE的二进制概念与日期的字符串表示形式混淆。字符串只是一个字符串，您作为人类识别为数据，但是对于计算机来说，'2〜14'没有比hereisyoursign更多的含义。

I've been wondering - I have a lot of use with sysdate on my system, and when comparing it to my date columns I have to use trunc(sysdate) since the format of sysdate is DD/MM/YYYY HH24:MI:SS .

I looked over the internet for other functions to return current date on format of DD/MM/YYYY but had no luck, current_date , current_timestamp and ETC also gives me the hours format..

I need this for better performance when comparing an indexed date column to the current date.

So , anybody know of a system function that returns the desired format? or a way to bypass it?

解决方案

If your "date" column is of type DATE, then you do not need to be concerned with "format". You need to compare trunc(column_name)to trunc(sysdate). And if that is causing serious performance issues because the use of a function eliminates the use of the index, then create a function-based index. A less elegant solution would be

WHERE mydatecol > trunc(sysdate) AND mydatecol < trunc(sysdate +1)

What you do NOT want to do is confuse the binary concept of DATE with the character string representation of a date. A character string is just a string of characters that you as a human recognize as a data, but to the computer, '2016-02-14' has no more meaning than 'hereisyoursign'.

这篇关于Oracle - 获取当前日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！