-1

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

Pistachio_Kulfi
  • 21
  • 4

2 Answers2

0

Duckdb hass ither literals for dates see manual

SELECT strptime('19920302', '%Y%m%d');
nbk
  • 45,398
  • 8
  • 30
  • 47
0

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.

rachelyw
  • 126
  • 10
  • 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 Feb 21 '23 at 19:19
  • 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 – Pistachio_Kulfi Feb 21 '23 at 19:21