CODEWITHSUNDEEP
sqlpostgresqlto-char
Taylan Morcol
Taylan Morcol

Reputation: 23

SQL: to_char not always returning text (PostgreSQL, DataCamp)

I'm working with a table called 'support', which has the field 'creation_date', which is a date type.

My goal is to convert creation_date to a text string in the format 'YYYY-MM-DD'.

When I run this code:

SELECT TO_CHAR(creation_date, 'YYYY-MM-DD') AS creation_date
  FROM support

The output is still displaying in the viewer as "DATETIME" rather than as text:

output 1

If I use a different character (i.e., not dash) as separators, it outputs as expected (i.e., text). For example,

SELECT TO_CHAR(creation_date, 'YYYY~MM~DD') AS creation_date
  FROM support

output like '2023~01~01', etc.

Funny enough, if I don't use any separators, the output is integer. For example,

SELECT TO_CHAR(creation_date, 'YYYYMMDD') AS creation_date
  FROM support

outputs as '20230101', etc., which is displayed in the viewer as "INTEGER".

Have I misunderstood something about how to_char works? I would think it's output should always be text.

For reference, I'm running PostgreSQL through DataCamp's online learning platform. I suspect this issue may stem from some quirk of DataCamp, but I'm not sure.

Upvotes: 0

Views: 54

Answers (1)

pgsupport
pgsupport

Reputation: 41

error : outputs as '20230101', etc., which is displayed in the viewer as "INTEGER" - (This may be due to Viewer Tool Interpretation, where the tool misinterprets string outputs containing only numeric characters as integers)

sample test for your reference:

1. SELECT id, creation_date, TO_CHAR(creation_date,'YYYY-MM-DD') AS formatted_date FROM support;

2. SELECT id, creation_date,TO_CHAR(creation_date, 'YYYYMMDD') AS formatted_date FROM support;

enter image description here

enter image description here

Upvotes: 0

Related Questions