TDS Foreign Data Wrapper connection from Aurora Postgres (16) to SQL Server RDS - date format

The issue you're experiencing is related to the date format mismatch between SQL Server and Aurora PostgreSQL when using the TDS Foreign Data Wrapper (tds_fdw). This is a known challenge when working with date/time fields across different database systems.

The error you're seeing is because the date format returned by SQL Server ("Jul 10 2020 01:58:20:AM") is not directly compatible with PostgreSQL's timestamp parsing. As you've noticed, the colon (:) before AM is causing the issue.

There are a few potential approaches to resolve this:

Attaching an AWS blog post that provides a detailed explanation of setting up of tds_fdw and outlines possible workarounds for issues related to DATE columns. Please refer to the section titled "Reconcile DATE-related column issues due to locale setting differences" in the blog.

[+] https://aws.amazon.com/blogs/database/use-the-tds_fdw-extension-to-migrate-data-from-sql-server-to-postgresql/

Custom Handling: Although you mentioned preferring not to add complexity, you might need to consider custom handling of the date/time fields. This could involve creating a view on the SQL Server side that formats the date in a PostgreSQL-compatible format, or using PostgreSQL functions to parse the incoming date string.

Below, I’ve summarized the workarounds provided in the blog for custom handling :

Workaround 1: Convert the Column Type
          a)Change the column type from DATE to DATETIME in SQL Server, as the DATETIME type maps to the PostgreSQL TIMESTAMP type.
          b)If you need only the date, you can cast the resulting column in the SELECT statement (e.g., SELECT column_name::date).


 Workaround 2: Use a View to Handle Data Conversion
          a)Create a view in SQL Server that wraps the DATE column and converts it to DATETIME.
           b)Import the view using FDW and query it in PostgreSQL.
          c)Cast the column to DATE in PostgreSQL during SELECT queries.


 Workaround 3: Convert DATE to TEXT on foreign table and Use to_date() Postgresql function
           a)Convert the DATE column in foreign table to TEXT. This can be done by dropping the existing DATE column and adding it    back  as TEXT.
           b)Use PostgreSQL’s to_date() function to convert the text column back to a DATE when querying.

PostgreSQL DateStyle: As you mentioned, changing the DateStyle parameter in PostgreSQL could potentially resolve the issue, but this should be done cautiously as it might affect other processes that are using the default postgresql datestyle

Given the complexity of working with heterogeneous databases, some level of custom handling or configuration adjustment is often necessary when dealing with data type discrepancies, especially for date/time fields. You might need to weigh the trade-offs between modifying global settings, adding custom handling logic, or adjusting the foreign data wrapper configuration to find the most suitable solution for your specific use case.
Sources [https://aws.amazon.com/blogs/database/use-the-tds_fdw-extension-to-migrate-data-from-sql-server-to-postgresql/] Working with SQL Server databases by using the tds_fdw extension - Amazon Aurora
Aurora PostgreSQL-Compatible integration with heterogeneous databases - AWS Prescriptive Guidance
Working with the supported foreign data wrappers for Amazon Aurora PostgreSQL - Amazon Aurora
[Integrating Amazon Aurora PostgreSQL-Compatible with heterogeneous databases and AWS services - AWS Prescriptive Guidance](https://docs.aws.amazon.com/prescriptive-guidance/latest/aurora-postgresql-integration/introduction.html