20 Jun 2023

DuckDB/SQL: Convert string in YYYYmmdd format to Date

I’ve been working with a data set that represents dates as strings in the format 'YYYYmmdd' and I wanted to convert those values to Dates in DuckDB. In this blog post, we’ll learn how to do that.

Let’s create a small table with a single column that represents date of births:

create table players (dob VARCHAR);
insert into players values('20080203'), ('20230708');

We can write the following query to return the rows in the table:

select * from players;

Table 1. Output
dob
20080203
20230708

At the moment the date of birth is a string, but we can use the strptime function to convert it to a timestamp:

SELECT strptime(dob, '%Y%m%d') AS dob
FROM players;

Table 2. Output
dob
2008-02-03 00:00:00
2023-07-08 00:00:00

I don’t really care about the time component though, so let’s get rid of that by casting it to a DATE:

SELECT cast(strptime(dob, '%Y%m%d') AS DATE) AS dob
FROM players;

Table 3. Output
dob
2008-02-03
2023-07-08

Job done!

About the author

I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.