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

admin管理员组

文章数量:1125925

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:

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.

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:

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.

• sql
• postgresql
• to-char

Share Improve this question Follow edited Jan 9 at 3:12 Taylan Morcol asked Jan 9 at 2:38 Taylan MorcolTaylan Morcol 2366 bronze badges 11

• 1 Its looks like you have some tool that you are using to "look" at the results that implicitly converts dates-stored-as-text to dates. Whatever it is that you are "looking" at these results with. – topsail Commented Jan 9 at 2:47
• Why convert at all, what effect do you expect? I think that's just useless. Anyway, your issue can't be replicated: db<>fiddle – Jonas Metzler Commented Jan 9 at 2:52
• 2 Maybe just use a better tutorial? People who follow a tutorial should get help and not be confused by unreproducible issues caused by bad tools that don't appear in real life. – Jonas Metzler Commented Jan 9 at 3:01
• 1 @DaleK Thanks for alerting me to the question guidelines regarding images. I wasn't aware of them. I edited the post to remove all but one image. I think the image I left in has some value in demonstrating what I'm seeing on my end. It seems to have clued in topsoil that the issue could be with the viewing tool. – Taylan Morcol Commented Jan 9 at 3:08
• 2 Go back to 1st principals. Ran your queries and I added pg_typeof() for each. ∀, type TEXT was returned. Also ran it on laptop (W2019 Server, PG17 - psql - I believe that db<>fiddle uses psql as its back-end) - same! Something is wrong with the DataCamp tool! Finally, I would strongly recommend against using anything but DATE to store DATE fields - you can confuse the optimiser and it's not good practice and try performing date arithmetic or extracting useful data (Year, Month, Day...) or adding an INTERVAL to TEXT - nightmare! – Vérace Commented 2 days ago

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 0

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;

本文标签： SQL tochar not always returning text (PostgreSQLDataCamp)Stack Overflow