Create or replace table stmt from PostGres to SQL converts Date into DateTime2

[sda]

Since I upgraded Query Tool and Data Hub to 22.0.30 (I’m not sure of the version it comes from, probably early 2022 PROD version), I noticed that the Create or replace table mytable@sql as select * from mytable@postgres stmt now converts Date into Datetime2 datatype

Is this an intended behavior?

Note

I upgraded my PostGres server from 9.5 to 14.3 which fired the required upgrade of the Invantive suite otherwise I was getting an error on metadata loading issue:

itgenpsr003: Could not load all metadata.
itgenpsr067: Er zijn primaire sleutels gevonden voor 60-tabellen, maar de bijbehorende tabellen zijn niet gevonden.
Controleer of de gebruiker daadwerkelijk tabellen of views in de database ‘’ kan opvragen.
(pg_catalog.pg_default_acl., pg_catalog.pg_tablespace., pg_catalog.pg_type., pg_catalog.pg_attribute., pg_catalog.pg_proc., pg_catalog.pg_class., pg_catalog.pg_authid., pg_catalog.pg_auth_members., pg_catalog.pg_database., pg_catalog.pg_foreign_server., pg_catalog.pg_user_mapping., pg_catalog.pg_sequence., pg_catalog.pg_foreign_data_wrapper., pg_catalog.pg_shdescription., pg_catalog.pg_aggregate., pg_catalog.pg_am., pg_catalog.pg_amop., pg_catalog.pg_amproc., pg_catalog.pg_attrdef., pg_catalog.pg_cast., pg_catalog.pg_constraint., pg_catalog.pg_conversion., pg_catalog.pg_description., pg_catalog.pg_index., pg_catalog.pg_inherits., pg_catalog.pg_language., pg_catalog.pg_largeobject., pg_catalog.pg_namespace., pg_catalog.pg_opclass., pg_catalog.pg_operator., pg_catalog.pg_rewrite., pg_catalog.pg_statistic., pg_catalog.pg_trigger., pg_catalog.pg_opfamily., pg_catalog.pg_db_role_setting., pg_catalog.pg_largeobject_metadata., pg_catalog.pg_extension., pg_catalog.pg_foreign_table., pg_catalog.pg_policy., pg_catalog.pg_partitioned_table., pg_catalog.pg_statistic_ext., pg_catalog.pg_init_privs., pg_catalog.pg_statistic_ext_data., pg_catalog.pg_collation., pg_catalog.pg_event_trigger., pg_catalog.pg_enum., pg_catalog.pg_range., pg_catalog.pg_transform., pg_catalog.pg_shseclabel., pg_catalog.pg_seclabel., pg_catalog.pg_ts_dict., pg_catalog.pg_ts_parser., pg_catalog.pg_ts_config., pg_catalog.pg_ts_config_map., pg_catalog.pg_ts_template., pg_catalog.pg_replication_origin., pg_catalog.pg_subscription., pg_catalog.pg_subscription_rel., pg_catalog.pg_publication., pg_catalog.pg_publication_rel.)

[forums]

You are right. Since version 20.2.242 the default date time data type on SQL Server is datetime2.

Please refer to Use of datetime2 on SQL Server instead of datetime for more information.

According to the Microsoft documentation on that data type there are some advantages using datetime2 over datetime:

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

This is exactly the reason this decision was made. If you want to force specific data type on your table columns, create the table by hand in SQL Server Management Studio, since there is currently no way to specify a deviating datatype mapping using Invantive SQL.

[forums]

Are you still experiencing the PostgreSQL error? If so, please create another forum post so we can handle that separately from the SQL Server question.

[sda]

Thanks for your reply.

I understand what you say, and makes senses for Datetime format.

However, the source format from Postgres is Date, not Datetime. The Date format exists in SQLServer.

What we have now:

Postgres	SQL Server
Date	DateTime2

[sda]

No, the PostgreSQL error does not pop up anymore since the upgrade.

[forums]

I understand now. Since the data is converted from PostgreSQL to .NET (the internal code engine Invantive SQL is written in), and then from .NET to SQL Server, there is one more component involved, and that gives the trouble here.

Historically, .NET did not have a Date or Time data type. Since .NET 6, which was released a few months ago, it does have such data types (DateOnly and TimeOnly). These are not (yet) implemented in Invantive SQL, so the only conversion we can make now is to a data type that supports both date and time. That’s why it is not possible to convert a PostgreSQL date to a SQL Server date.

The mapping from PostgreSQL Date to SQL Server DateTime2 is therefor intended behavior.

In case it causes any errors, please add a reply.

[forums]

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.