英文:
Presto SQL - Trouble with converting date in varchar to date format
问题
在Presto-SQL中，我在将varchar格式的日期列转换为日期格式时遇到了问题。日期列的格式如下：'9/5/2022'，'12/23/2022'，'%null%'等。
我尝试了以下查询：
SELECT
DATE_PARSE(date_column, '%m/%d/%Y')
FROM table
SELECT
CAST(date_column AS DATE)
FROM table
但总是出现以下错误：
Caused by: io.prestosql.jdbc.$internal.client.FailureInfo$FailureException: Invalid format: "11/30/2022" is malformed at "/30/2022"
英文:
I am having an issue converting a date column in varchar format to date format in presto-sql. The date_column is in this format : '9/5/2022', '12/23/2022', '%null%' etc.
I have tried the following queries:
SELECT
DATE_PARSE(date_column, '%Y-%m-%d')
FROM table
SELECT
CAST(date_column AS DATE)
FROM table
And there is always this error popping up:
> Caused by: io.prestosql.jdbc.$internal.client.FailureInfo$FailureException: Invalid format: "11/30/2022" is malformed at "/30/2022"
答案1
得分: 1
The provided format (see the docs) represents year, month, day separated by - while your data is in month, day, year separated by /. Change the format accordingly:
Output:
SELECT DATE_PARSE('11/30/2022', '%m/%d/%Y');
Output:
_col0
-------------------------
2022-11-30 00:00:00.000
Note that strings like '%null%' (not just null strings) will be invalid also. If you really have those - you should consider wrapping the parse call into try.
英文:
The provided format (see the docs) represents year, month, day separated by - while your data is in month, day, year separated by /. Change the format accordingly:
SELECT DATE_PARSE('11/30/2022', '%m/%d/%Y');
Output:
_col0
-------------------------
2022-11-30 00:00:00.000
Note that strings like '%null%' (not just null strings) will be invalid also. If you really have those - you should consider wrapping the parse call into try.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论