RE: Order of operations in postgreSQL.

From your stackoverflow example. (please don't post links, they will be hard to use in future if link expires)

This is what is happening.

Your code: TO_TIMESTAMP( trim ( substring(m.text_value, 1, 19)), 'YYYY-MM-DD"T"HH24:MI:SS' ) as tms

Your ERROR: ERROR: invalid value "test" for "YYYY"

Postgres logs are very clear about what is going on, if you step back and read them.

Test is in '', the error says 'test' is invalid for YYYY

Reproduced by:

SELECT TO_TIMESTAMP( trim ( substring('test00000000000000', 1, 19)), 'YYYY-MM-DD"T"HH24:MI:SS' )

Returns: ERROR: invalid value "test" for "YYYY" DETAIL: Value must be an integer.

From: Ken McClaren <ken(dot)mcclaren(at)kipuhealth(dot)com>
Sent: Wednesday, May 31, 2023 10:49 AM
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: John Hall <john(dot)hall(at)kipuhealth(dot)com>
Subject: [EXTERNAL] Order of operations in postgreSQL.

https://stackoverflow.com/questions/76339408/gracefully-handle-datatime-in-postgresql/76345292?noredirect=1#comment134659364_76345292<https://urldefense.com/v3/__https:/stackoverflow.com/questions/76339408/gracefully-handle-datatime-in-postgresql/76345292?noredirect=1*comment134659364_76345292__;Iw!!GFE8dS6aclb0h1nkhPf9!8Gbv5mGohfEUVtNgQr_HkTXYFeKYHD0lMrw5Y6OTGKntYkX1Qx48MamQOTZ0rwUKgbMWIYRORorLk5i2akjNuSmiQZJf6SFqLQJiRzg$>

Just a few notes on this issue:

PSQL:
Appears to be applying the filter to all the columns in the table after the join.

TSQL:
Tried to duplicate this behavior and got these results.

select t1.id as t1id,
cast(cast(t1.test_value as varchar(max)) as datetime) as tms
from table1 t1
join table2 t2
on t1.id = t2.id
where cast(cast(t1.test_value as varchar(max)) as datetime) > '2023-05-27'

These statements work as expected in SQL Server.

Let me know if you have any questions.

[Logo Description automatically generated]<https://urldefense.com/v3/__http:/kipuhealth.com/__;!!GFE8dS6aclb0h1nkhPf9!8Gbv5mGohfEUVtNgQr_HkTXYFeKYHD0lMrw5Y6OTGKntYkX1Qx48MamQOTZ0rwUKgbMWIYRORorLk5i2akjNuSmiQZJf6SFqRvV4zTs$>

Ken McClaren
Database Administrator

864.313.5997
Easley, SC
Instagram<https://urldefense.com/v3/__https:/www.instagram.com/kipuhealth/__;!!GFE8dS6aclb0h1nkhPf9!8Gbv5mGohfEUVtNgQr_HkTXYFeKYHD0lMrw5Y6OTGKntYkX1Qx48MamQOTZ0rwUKgbMWIYRORorLk5i2akjNuSmiQZJf6SFqgoUJt80$> | LinkedIn<https://urldefense.com/v3/__https:/www.linkedin.com/company/kipuhealth__;!!GFE8dS6aclb0h1nkhPf9!8Gbv5mGohfEUVtNgQr_HkTXYFeKYHD0lMrw5Y6OTGKntYkX1Qx48MamQOTZ0rwUKgbMWIYRORorLk5i2akjNuSmiQZJf6SFq4jhwZ7o$> | Facebook<https://urldefense.com/v3/__https:/www.facebook.com/KipuSystems/__;!!GFE8dS6aclb0h1nkhPf9!8Gbv5mGohfEUVtNgQr_HkTXYFeKYHD0lMrw5Y6OTGKntYkX1Qx48MamQOTZ0rwUKgbMWIYRORorLk5i2akjNuSmiQZJf6SFqmAVwRsA$> | Twitter<https://urldefense.com/v3/__https:/twitter.com/kipuhealth__;!!GFE8dS6aclb0h1nkhPf9!8Gbv5mGohfEUVtNgQr_HkTXYFeKYHD0lMrw5Y6OTGKntYkX1Qx48MamQOTZ0rwUKgbMWIYRORorLk5i2akjNuSmiQZJf6SFqhlgrdYI$>
kipuhealth.com <https://urldefense.com/v3/__http:/kipuhealth.com/__;!!GFE8dS6aclb0h1nkhPf9!8Gbv5mGohfEUVtNgQr_HkTXYFeKYHD0lMrw5Y6OTGKntYkX1Qx48MamQOTZ0rwUKgbMWIYRORorLk5i2akjNuSmiQZJf6SFqRvV4zTs$>