I have this SQL code that generate the same result. which one is most efficient?

SELECT SUBSTRING(transaccion_day, 1, 7) FROM my_table

SELECT to_char(to_date(transaccion_day,'YYYY-MM-DD'),'YYYY-MM') FROM my_table

• The content of transaccion_day is something like this: '2022-06-09'
• The result is something like: '2022-06'

CodePudding user response：

It is most efficient with substring because in the second option you need to do two converts

CodePudding user response：

If it has to be a text column (I hope not), then simply use a like query. It would utilize the existing index on the column. ie:

 SELECT * FROM my_table 
where transaccion_day like '2022-06%';