Let's share some little snippets of code that we find useful. No snippet too small! If you found it useful so may someone else
Retrosheet db
Extracting dates from the GAME_ID column in the GAMES table
-- extract year (date datatype)
year(substr(game_id,4,8))
-- example
select
year(substr(game_id,4,8)) as 'Game Year',
games.*
from games
where game_id = 'LAN197604270';
-- extract date (date datatype) in YYYY-MM-DD format from GAME_ID column in GAMES table
cast(concat
(
substr(game_id,4,4),
'-',substr(game_id,8,2),
'-',substr(game_id,10,2)
)
as date)
-- example
select
cast(concat
(
substr(game_id,4,4),
'-',substr(game_id,8,2),
'-',substr(game_id,10,2)
) as date) as 'Game Date',
games.*
from games
where game_id = 'LAN197604270';