英文:
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 "TABLE"
. Similarly, Date
is a mixed-case identifier and must likewise be quoted (e.g. "Date"
) every time it's used.
Given the above your query becomes:
SELECT TO_CHAR("Date", 'DD/MM/YY HH24:MI:SS') AS FORMATTED_DATE
FROM "TABLE"
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论