postgresql Postgres 字符串到日期示例 10Apr77 到 10/04/1977
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17067372/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres String to Date EXAMPLE 10Apr77 to 10/04/1977
提问by lemondash
Good afternoon all,
大家下午好
I'm fairly new to PostgreSQL, so apologies in advance if my question is below the average.
我对 PostgreSQL 还很陌生，所以如果我的问题低于平均水平，请提前道歉。
I'm receiving some data in a csv flay file format and is nicely being insert in to a staging table for me to preview the data. On of the things i have an problem with is the data field is store as a string value. For example, 10Apr96 - 01Jan85
我正在接收一些 csv flay 文件格式的数据，并且很好地插入到临时表中以供我预览数据。我遇到的问题之一是数据字段存储为字符串值。例如，10Apr96 - 01Jan85
I've wrote the below query to see if could break the string up and concat back together in a date format.
我已经编写了以下查询，以查看是否可以将字符串分解并以日期格式重新连接在一起。
Is there an easier way to achieve what I'm doing ?
有没有更简单的方法来实现我正在做的事情？
select
concat(cast(substring(datetext,1,2) as varchar(2)),'/',case
when cast(substring(datetext,3,3) as varchar(3)) = 'Jan' then '01'
when cast(substring(datetext,3,3) as varchar(3)) = 'Feb' then '02'
when cast(substring(datetext,3,3) as varchar(3)) = 'Mar' then '03'
when cast(substring(datetext,3,3) as varchar(3)) = 'Apr' then '04'
when cast(substring(datetext,3,3) as varchar(3)) = 'May' then '05'
when cast(substring(datetext,3,3) as varchar(3)) = 'Jun' then '06'
when cast(substring(datetext,3,3) as varchar(3)) = 'Jul' then '07'
when cast(substring(datetext,3,3) as varchar(3)) = 'Aug' then '08'
when cast(substring(datetext,3,3) as varchar(3)) = 'Sep' then '09'
when cast(substring(datetext,3,3) as varchar(3)) = 'Oct' then '10'
when cast(substring(datetext,3,3) as varchar(3)) = 'Nov' then '11'
when cast(substring(datetext,3,3) as varchar(3)) = 'Dec' then '12'
end,'/',cast(substring(datetext,6,2) as varchar(2))) as dt
from tbl_loading_horses_tmp
回答by user2478690
You can use the to_date()function, which takes a format string of how to interpret the input and returns a datevalue which is what you should be storing in your DB.
您可以使用该to_date()函数，它采用一个格式字符串来解释输入并返回一个date值，该值是您应该存储在数据库中的值。
SELECT to_date('10Apr96', 'DDMonYY');
to_date
------------
1996-04-10
(1 row)
To convert that back to a string in a different format when retrieving for display, use the to_char()function documented on the same page.
要在检索显示时将其转换回不同格式的字符串，请使用to_char()同一页面上记录的函数。
