将日期转换为 SQL 中的字符格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21914444/
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
Converting date to character format in SQL
提问by Bart Schuijt
I'm trying to convert a date format stored m/d/yyyy to a 'yyyymmdd' character format, without the need to cast the date column every time I want to use the column in my query.
我正在尝试将存储 m/d/yyyy 的日期格式转换为 'yyyymmdd' 字符格式，而无需每次我想在查询中使用该列时都转换日期列。
Currently I'm casting and formatting dates as chars: ((cast(cast(invitation_date as CHAR(8)) as date format 'YYYYMMDD')).
目前我正在将日期转换和格式化为 chars: ((cast(cast(invitation_date as CHAR(8)) as date format 'YYYYMMDD'))。
Is there a way to convert the data column once and call the converted character value later in the query?
有没有办法将数据列转换一次并稍后在查询中调用转换后的字符值？
I'm using Teradata in Aqua Data Studio 13.0.3.
我在 Aqua Data Studio 13.0.3 中使用 Teradata。
回答by man
select to_char(field, 'yyyy-mm--dd hh:mm:ss') from table;
回答by tariq
Navigate to File->Options -> Results Format -> Teradata. Then select the datatype Date and enter yyyyMMdd. Your result set will now return the specified date format. Let me know if this will solve your issue.
导航到文件 -> 选项 -> 结果格式 -> Teradata。然后选择数据类型 Date 并输入 yyyyMMdd。您的结果集现在将返回指定的日期格式。让我知道这是否能解决您的问题。
回答by dnoeth
If source is a DECIMAL yyyymmdd you can do
如果源是 DECIMAL yyyymmdd 你可以做
CAST(invitation_date - 19000000 AS DATE) AS newcol
Teradata allows an alias to be used in any place, so you can simply do
Teradata 允许在任何地方使用别名，因此您只需执行以下操作
WHERE newcol > DATE
Of course best case would be to change those columns to DATE during load.
当然，最好的情况是在加载期间将这些列更改为 DATE。
回答by Iciyas
You shouldn't put it like this, it simply won't work:
你不应该这样说，它根本行不通：
select invitationdate(field, 'yyyymmdd')
try this instead :
试试这个：
SELECT to_char(column_date, 'yyyy/mm/dd') from table;
as "man" said. please pay attention to the undescore (_) character; not this (-) one.
正如“人”所说。请注意下划线 ( _) 字符；不是这个 ( -) 一个。
回答by Dimitar Nentchev
Here is a longer but in a way simpler way that works
这是一个更长但更简单的工作方式
select yourdate ,cast((extract(year from yourdate)*100 + extract(month from yourdate))*100 + extract(day from yourdate) as char(8)) as yyyymmdd from ...
select yourdate ,cast((extract(year from yourdate)*100 + extract(month from yourdate))*100 + extract(day from yourdate) as char(8)) as yyyymmdd from ...
