SQL query recognises 1/7/2021 as the mm/dd/yyyy format how to change that to dd/mm/yyyy format

title mostly explains it but when performing an sql query with (for example:) '1/7/2021/::timestamp in it, it recognises it as a mm/dd/yyyy. How to change it to dd/mm? I've tried messing with the localisation settings in admin but it didn't seem to help.

Hi @Alexhs
Post "Diagnostic Info" from Admin > Troubleshooting.
You need to post more of your query and which database you're using.
Most databases only understands yyyy-mm-dd, since that's universal, whereas other formats are dependent on countries.

Hi @flamber
I tried to dm you about the diagnostic info. switching it to yyyy/mm/dd did the trick. here's the code,

select  count(*) as count
from users
where date_trunc('month', users.created) <= '1/7/2021'::timestamp
and date_part('year',"public"."users"."created") = date_part('year', CURRENT_DATE)
group by date_trunc('month', users.created)

I've done a few queries like this without realising the timestamp was being read as mm/dd/yyyy so wanted to see if I can change them all in one go with admin settings without redoing each query.

@Alexhs I only provided direct support for Enterprise customers. Everyone else has to post in the free public forum, so everybody can learn from it.
Since it looks like you're using Postgres, the correct format would be '2021-07-23'::timestamp - but it has nothing to do with Metabase, that's up to your database to determine formatting (sometimes configurable)
You would have to manually change each query (since it's basically "wrong"), or do a search-replace in the Metabase application database table report_card

1 Like

@flamber sure, understandable.
Ok thanks I'll look into changing the database settings or jus manually change them, thanks for the help