BuzzCutNorman / tap-mssql

Singer Tap for MS SQL built with Meltano Singer SDK.
MIT License
2 stars 9 forks source link

feat: Support for `datetime2` data type #32

Open BuzzCutNorman opened 1 year ago

BuzzCutNorman commented 1 year ago

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.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver16

https://docs.sqlalchemy.org/en/14/dialects/mssql.html#sqlalchemy.dialects.mssql.DATETIME2

BuzzCutNorman commented 1 year ago

The definition of DATETIME2 in JSON Schema format can be achieved with the following

{
    "type": ["string"],
    "pattern": "^[0-9]{4}-[0-1][0-9]-[0-3][0-9]T[0-2][0-3]:[0-5][0-9]:[0-5][0-9]\\.[0-9]{1,7}\\+00:00$",
    "format": "date-time"
}

The pattern: will validate via REGEX that the string conforms to the pendulum ISO format string a tap outputs for a DATETIME2 column. We use format: to get the target to place the string into a datetime variable so SQLAlchemy can add only the sections is needs for DATETIME2.

I just need to get pattern: added to the SDK SCHEMA data class.