Hi does anyone know how to convert a string in YYYYMMDD format to yyyy-mm-dd in duck db?
have tried to cast, have tried strptime(date, 'yyyymmdd'), have tried convert, to_date
3 Answers
Same as nbk's answer, however make sure that the variable is a VARCHAR and not a INT. If it is a INT use:
SELECT strptime(CAST(integer_date AS VARCHAR), '%Y%m%d');
Comments
You can do so like this
SELECT strftime(DATE your_string, '%Y-%m-%d');
See duckdb documentation for more information :)
You could also try converting the string first outside of duck db like so:
string.strftime('%Y-%m-%d')
and then passing it to duck db.
2 Comments
Pistachio_Kulfi
I'm getting a syntax error with this:
with a as ( select *, cast(date as text) as string_date from raw_data ) select * , strftime(date string_date, '%Y-%m-%d') from a ParserException: Parser Error: syntax error at or near "string_date" LINE 6: select * , strftime(date string_date, '%Y-%m-%d') from a ^Pistachio_Kulfi
It's like the error is between date and string and I don't understand why? Do you have any ideas? Data is definitely in yyyymmdd format