Custom DATE format for ODBC (PostgreSQL)

Toad Data Point & Toad Intelligence Central
1

Hi,

I’m using TDP 4.2.0.207 to connect to a PostgreSQL database. The thing is I want the DATE and the DATETIME columns to be formatted in a different way:

Date = yyyy-MM-dd
TimeStamp = yyyy-MM-dd hh:mm:ss

I’ve tried to set up a custom format just for the dates by configuring:

Odbc:DATE=yyyy-MM-dd, (as mentioned in a very old thread here)

but this doesn’t seem to work. I’m pretty sure that technique works in “Toad for SQL Server”, where it is possible to have different formatting, but I’m not sure if TDP allows this for ODBC.

Does anybody know if this is feasible at all?

2

Hello,

it looks like that ODBC is limited in this. You can change the formation of DateTime in Options-Grid-Data Type Formatting to desired format, or write your own SQL query and use: "TO_CHAR(date_, 'YYYY-Mon-DAY') " to force datetime format.

I hope its helpful.

Best regards Jakub.

1018.Gridformat.png
1018.Gridformat.png1254×911 83.3 KB

3

You can always break it up using substr and instr and then bringing it back together in the format you want.

From: veskojl [mailto:bounce-veskojl@toadworld.com]

Sent: Wednesday, June 14, 2017 12:13 AM

To: toaddatapoint@toadworld.com

Subject: [Toad Data Point - Discussion Forum] Custom DATE format for ODBC (PostgreSQL)

Custom DATE format for ODBC (PostgreSQL)

Thread created by veskojl

Hi,

I’m using TDP 4.2.0.207 to connect to a PostgreSQL database. The thing is I want the DATE and the DATETIME columns to be formatted in a different way:

Date = yyyy-MM-dd

TimeStamp = yyyy-MM-dd hh:mm:ss

I’ve tried to set up a custom format just for the dates by configuring:

Odbc:DATE=yyyy-MM-dd, (as mentioned in a very old thread here)

but this doesn’t seem to work. I’m pretty sure that technique works in “Toad for SQL Server”, where it is possible to have different formatting, but I’m not sure if TDP allows this for ODBC.

Does anybody know if this is feasible at all?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

4

Thank you both. I know about this workarounds, the problem is how to fix this permanently. DATE and DATETIME are two different data types, so they are supposed to be handled differently and I’m pretty sure I was able to achieve this behavior in “Toad for SQL Server”, so I was hoping the same could be applied to the more general ODBC connection type.

5

I create a task QAT-10874 to track the request.

Regards

Aleksey

6

If your date and datetime data are going into two different fields then use two different To_Char or To_Date/To_Timestamp functions. To_Char will format it how you want, To_Date will convert any string date to a date value, To_Timestamp to a date/time. To_Char(YourDateField, ‘YYYY-MON-DD’)/To_Char(YourTimeStamp, ‘YYYY-MON-DD HH24:MI:SS’) to convert the other way you need to know whether you are convtering a date string to a date value or a timestamp string to a datetime value as there are two different functions (To_Date and To_Timestamp). Maker sure your PostgreSQL ODBC driver is up to date or matches the Postgre database you are pulling the data from.