从时间戳获取Oracle中的日期而不尾随O? [英] Getting date in oracle from timestamp without trailing o's?

问题描述

我一直在搜索堆栈和Google，以找到一种从时间戳中提取日期的方法，而尾随零仍然是日期而不是VARCHAR变量-

I have been scouring stack and google to find a way to extract a date from timestamp w/o the trailing zero's still being a date and NOT a VARCHAR variable-

例如:

SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL

给出: 18-JUL-12 00:00:00

最后要以 '18/07/2012'日期格式显示.

I want it in '18/07/2012' date format in the end.

TIA.

推荐答案

我一直在搜索堆栈和Google，以找到一种从时间戳中提取日期的方法，而尾随零仍然是日期，而不是 VARCHAR 变量

如果您希望该值仍为 DATE

DATE 在内部存储为 7字节，并且始终为年份(2字节))，月，日，时，分和秒(每个1字节)组成部分.

A DATE is stored internally as 7-bytes and always has year (2 bytes), month, day, hour, minute and second (1 byte each) components.

TIMESTAMP 在内部以11-20字节存储，具有与 DATE 相同的年至秒组成部分，但还包括小数秒组成部分和可选的时区组成部分.

A TIMESTAMP is stored internally with 11-20 bytes with the same year-to-second components as a DATE but also fractional seconds components and optional time zone components.

DATE 和 TIMESTAMP 都不具有格式，因为它们只是二进制数据，您不能删除时间分量，因为它们都始终具有时间分量.

Neither a DATE nor a TIMESTAMP has a format because they are just binary data and you cannot remove the time component because they both always have a time component.

SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL

给出: 18-JUL-12 00:00:00

查询将输出一个 DATE ，然后根据您使用的用户界面，日期可能会隐式转换为要输出的字符串.SQL/Plus和SQL Developer使用 NLS_DATE_FORMAT 会话参数在向用户显示 DATE 时将其隐式转换为字符串:

The query outputs a DATE and depending on the user interface that you use then the date may be implicitly cast to a string for outputting. SQL/Plus and SQL Developer use the NLS_DATE_FORMAT session parameter to implicitly convert DATEs to strings when it is displaying it to the user:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

输出: 2012-07-18 13:27:18

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

输出: 2012-07-18

但是， ANY 用户可以在 ANY 时间更改其自己的会话参数，因此您永远不应依赖日期的隐式格式.

However, ANY user can change their own session parameters at ANY time so you should never rely on implicit formatting of dates.

如果您想使用 DATE ，则不应期望它以任何特定的方式进行格式化，因为它只是二进制数据.如果要格式化日期，则应使用 TO_CHAR :

If you want a DATE then you should not expect it to be formatted in any particular way as it is just binary data. If you want a formatted date then you should explicitly convert it to a string with the formatting you require using TO_CHAR:

SELECT TO_CHAR(
         TRUNC( to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS') ),
         'DD/MM/YYYY'
       )
FROM   DUAL;

输出: 2012/07/18

db<>小提琴

这篇关于从时间戳获取Oracle中的日期而不尾随O?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！