DOMO SQL MAX(date) not working

Nek

hello, hope someone could help on my SQL:

I just created a simple SQL under DOMO MySQL to select some fields and the MIN and MAX of a date, like:

SELECT `id`, `name`, min(`a_date`) as 'oldest date', max(`a_date`) as 'latest date'

FROM`data_1`

GROUP BY `id`, `name`

but nothing is returned in the 2 date fields, original dataset contain a valid date value in all rows.

I tried to simply select the MIN or MAX date without GROUP BY, it works properly, but it returns null once with GROUP BY. Any idea and solution?

thank you!

jaeW_at_Onyx

@Nek , Domo MySQL dataflows run on MySQL 5.6 databases. If you write valid MySQL code it should run.

Your code looks fine so I'm surprised you're not getting results.

Keep in mind the preview will only show the first 100? rows AND default behavior when building dataflows is for domo to only ingest the first 10k? rows, so maybe in the set of data you're aggregating there are no dates for that particular ID and Name.

Try

SELECT Count(*)
FROM `table`
WHERE a_date is not null and id is not null and name is not null

Then

SELECT `id`, `name`, min(`a_date`) as 'oldest date', max(`a_date`) as 'latest date'
FROM`data_1`
where a_date is not null
GROUP BY `id`, `name`

Nek

thanks jaeW_at_Onyx for your reply and advice! I did check all records contain a valid date, also tried adding:

where a_date is not null

or

where a_date '1900-01-01'

but no luck... i just process it ahead and see what the actual outcome would be

besides, I created the same with ETL, in preview, I can find a date but not get the correct date, e.g. a case with oldest date in 2017 and latest date in 2019, both my created min and max columns show date in 2019...

not sure if i cannot do both min and max on date at once!?

thanks.

MarkSnodgrass

You might try forcing a convert to the date data type to make sure MySQL is properly reading them as dates.