将日期转换为字符串

huangapple go评论57阅读模式
英文:

How to convert a date to a string

问题

我想要从日期列中仅获取“日期小时:分钟:秒”的部分,并且我想要获取一个包含日期小时:分钟:秒的字符串列。我尝试了以下代码,但它不起作用:

select *, TO_CHAR(extract(hour from (Date))) || ':' || TO_CHAR(extract(minute from (Date))) || ':' || TO_CHAR(extract(second from (Date))) as Date_string
from table;

希望这对你有所帮助。

英文:

I want yo get only the 'date hours:minutes:seconds' from the Date column

Date
10/11/22 12:14:01,807000000
11/12/22 13:15:46,650000000
29/12/22 14:30:46,501000000

and I want to get a string column with date hours:minutes:seconds

Date_string
10/11/22 12:14:01
11/12/22 13:15:46
29/12/22 14:30:46

I tried this code but it doesn't work:

select*, TO_CHAR(extract(hour from (Date)))||':'||TO_CHAR(extract(minute from (Date)))||':'||TO_CHAR(extract(second from (Date))) as Date_string
from table;

答案1

得分: 2

如果这是一个 date 列,你可以直接使用 to_char 函数:

SELECT m.*, TO_CHAR(my_date_column, 'dd/mm/yy hh24:mi:ss')
  FROM mytable m
英文:

If this is a date column, you could use to_char directly:

SELECT m.*, TO_CHAR(my_date_column, 'dd/mm/yy hh24:mi:ss')
  FROM mytable m

答案2

得分: 0

你可以使用正则表达式子字符串函数来获取左侧的日期字符串。

SELECT  REGEXP_SUBSTR (Date_string, '[^,]+', 1, 1)    
    AS left_part
   FROM Table1;

其中 [^,] 表示查找第一个位置上不是逗号的字符,并获取第一个出现的字符(在左侧)。

结果:

LEFT_PART
10/11/22 12:14:01
11/12/22 13:15:46
29/12/22 14:30:46

参考链接:
https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm

英文:

You can use REGEX SUBSTRING function to get the date string on the left.

SELECT  REGEXP_SUBSTR (Date_string, '[^,]+', 1, 1)    
    AS left_part
   FROM Table1;

where ^, means look for chars that are NOT comma on 1st position 
and get the first occurrence (on the left)

Result:

LEFT_PART
10/11/22 12:14:01
11/12/22 13:15:46
29/12/22 14:30:46

reference:
https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm

答案3

得分: 0

只需使用 TO_DATE()TO_CHAR() 函数配对,它们都在 Oracle 日期格式字符串上操作:

建立情景:

-- 你的输入 ..
WITH indata(dt) AS (
SELECT '10/11/22 12:14:01,807000000' FROM dual UNION ALL
SELECT '11/12/22 13:15:46,650000000' FROM dual UNION ALL
SELECT '29/12/22 14:30:46,501000000' FROM dual
)
-- 你的输入结束。真正的查询从这里开始。
-- 将以下逗号改为 "WITH" ..
,
-- 现在转换为 TIMESTAMP(9) ...
as_ts AS (
  SELECT
    TO_TIMESTAMP(dt ,'DD/MM/YY HH24:MI:SS,FF9') AS ts
  FROM indata
)
SELECT
  ts
, CAST(ts AS TIMESTAMP(0)) AS recast -- 注意:这是四舍五入的
, TO_CHAR(ts,'DD/MM/YY HH24:MI:SS') AS reformatted -- 这是截断的
FROM as_ts

结果:

TS RECAST REFORMATTED
10-NOV-22 12.14.01.807000000 10-NOV-22 12.14.02 10/11/22 12:14:01
11-DEC-22 13.15.46.650000000 11-DEC-22 13.15.47 11/12/22 13:15:46
29-DEC-22 14.30.46.501000000 29-DEC-22 14.30.47 29/12/22 14:30:46
英文:

Just do it with the TO_DATE() and TO_CHAR() function pair, both operating on the Oracle date format strings:

Building the scenario:

-- your input ..
WITH indata(dt) AS (
SELECT '10/11/22 12:14:01,807000000' FROM dual UNION ALL
SELECT '11/12/22 13:15:46,650000000' FROM dual UNION ALL
SELECT '29/12/22 14:30:46,501000000' FROM dual
)
-- end of your input. Real query starts here. 
-- Change following comma to "WITH" ..
,
-- Now convert to TIMESTAMP(9) ...
as_ts AS (
  SELECT
    TO_TIMESTAMP(dt ,'DD/MM/YY HH24:MI:SS,FF9') AS ts
  FROM indata
)
SELECT
  ts
, CAST(ts AS TIMESTAMP(0)) AS recast -- note: this is rounded
, TO_CHAR(ts,'DD/MM/YY HH24:MI:SS') AS reformatted -- this is truncated
FROM as_ts

Result:

TS RECAST REFORMATTED
10-NOV-22 12.14.01.807000000 10-NOV-22 12.14.02 10/11/22 12:14:01
11-DEC-22 13.15.46.650000000 11-DEC-22 13.15.47 11/12/22 13:15:46
29-DEC-22 14.30.46.501000000 29-DEC-22 14.30.47 29/12/22 14:30:46

答案4

得分: 0

根据您在问题中提供的信息,似乎Date字段中的数据是时间戳。这并不是问题,但表名(TABLE)和字段(Date)的命名存在一些挑战。

在Oracle中,TABLE是一个保留字,因此要将其用作表名,必须将其放在双引号内进行引用,如"TABLE"。同样,Date是一个混合大小写的标识符,每次使用时也必须加引号,例如"Date"

基于上述情况,您的查询应如下所示:

SELECT TO_CHAR("Date", 'DD/MM/YY HH24:MI:SS') AS FORMATTED_DATE
FROM "TABLE"

这将产生所期望的结果。在此处查看db<>fiddle

一般来说,在Oracle中最好避免使用保留字作为标识符,并允许数据库将所有名称转换为大写 - 这样您就不必引用名称,可以按大小写来引用它们,因为数据库会自动将所有未引用的名称内部转换为大写。

英文:

Going by what you have in your question, it appears that the data in the field Date is a timestamp. This isn't a problem, but the names of the table (TABLE) and field (Date) present some challenges.

In Oracle, TABLE is a reserved word - so to use it as the name of a table it must be quoted by putting it inside double-quotes, as &quot;TABLE&quot;. Similarly, Date is a mixed-case identifier and must likewise be quoted (e.g. &quot;Date&quot;) every time it's used.

Given the above your query becomes:

SELECT TO_CHAR(&quot;Date&quot;, &#39;DD/MM/YY HH24:MI:SS&#39;) AS FORMATTED_DATE
  FROM &quot;TABLE&quot;

and produces the desired results. db<>fiddle here

Generally, it's best in Oracle to avoid using reserved words as identifiers, and to allow the database to convert all names to upper case - if you do that you don't have to quote the names, and you can refer to them by upper or lower case as the database automatically converts all unquoted names to upper case internally.

huangapple
  • 本文由 发表于 2023年2月6日 22:13:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/75362452-2.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定