datefmt question

[ondohotola]

Given a file a.csv

with content

create_date,expiry_date
26-May-22,26-May-26

how do I change the dates to ISO?

[ondohotola]

A really ugly workaround is something like

qsv replace -q '\-' ' ' -s1,2 a.csv | qsv datefmt 1,2

but should of course be a regex of sorts

[jqnatividad]

I'm afraid the format you have in a.csv is not one of the supported formats.

And yes, your workaround is one way to get around this. Here's one that works for me:

$ qsv replace -s 1,2 '(\d+)-([A-Za-z]+)-(\d+)' '20$1-$2-$3' a.csv --quiet | qsv datefmt 1,2
create_date,expiry_date
2026-05-22,2026-05-26

[jqnatividad]

Alternatively, you can use the luau command to do more complicated date formatting/arithmetic.

See

qsv/tests/test_luau.rs

Lines 1825 to 1853 in 7f31b19

	#[test]
	fn luau_map_require_luadate() {
	let wrk = Workdir::new("luau");
	wrk.create(
	"data.csv",
	vec![
	svec!["letter", "number", "date_col"],
	svec!["a", "13", "2001-09-11"],
	svec!["b", "24", "1989-11-09"],
	svec!["c", "72", "2008-11-04"],
	svec!["d", "7", "2020-03-11"],
	],
	);
	let mut cmd = wrk.command("luau");
	cmd.arg("map")
	.arg("days_added")
	.arg(r#"local date = require "date";local t_date = date(date_col):adddays(tonumber(number)); return tostring(t_date:fmt("${iso}"))"#)
	.arg("data.csv");
	let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
	let expected = vec![
	svec!["letter", "number", "date_col", "days_added"],
	svec!["a", "13", "2001-09-11", "2001-09-24T00:00:00"],
	svec!["b", "24", "1989-11-09", "1989-12-03T00:00:00"],
	svec!["c", "72", "2008-11-04", "2009-01-15T00:00:00"],
	svec!["d", "7", "2020-03-11", "2020-03-18T00:00:00"],
	];
	similar_asserts::assert_eq!(got, expected);
	}

and https://tieske.github.io/date/

[rickhg12hs]

Here's another option using qsv sqlp:

$ qsv sqlp --quiet a.csv "SELECT DATE(create_date,'%d-%b-%y'),DATE(expiry_date, '%d-%b-%y') FROM a"
create_date,expiry_date
2022-05-26,2026-05-26

[jqnatividad]

This is actually better than the workaround @rickhg12hs . Choosing this as the answer. :)

[ondohotola]

I like it too.