Babelfish for PostgreSQL provides the capability for PostgreSQL to work with applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL, the Microsoft SQL Server query and procedural language, so you don’t have to switch database drivers or rewrite all of your application queries.
This PR addresses the behavioural differences in CASTing of string literals to datetime, datetime2 and datetimeoffset. The following changes have been made to fix the issue :
Currently, we use native PG function named ParseDateTime for parsing the input string literals. This function does not handle some cases such as empty spaces between the fields. We have created a function named cleanup_input_str which will modify the input string to remove all the unnecessary spaces and then call the inbuilt ParseDateTime function.
ParseDatetime splits the input string to different fields which then will be decoded in native function namedDecodeDateTime. But DecodeDatetime will not handle some cases such as accepting : as a separator for seconds and milliseconds. These additional cases were handled in a different function named tsql_decode_datetime_fields .
Also, if there is a text month we check the REGEX of the input str so that it matches the T-SQL behaviour
[ ] Commits are signed per the DCO using --signoff
By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 and PostgreSQL licenses, and grant any person obtaining a copy of the contribution permission to relicense all or a portion of my contribution to the PostgreSQL License solely to contribute all or a portion of my contribution to the PostgreSQL open source project.
For more information on following Developer Certificate of Origin and signing off your commits, please check here.
Description
This PR addresses the behavioural differences in CASTing of string literals to datetime, datetime2 and datetimeoffset. The following changes have been made to fix the issue :
ParseDateTimefor parsing the input string literals. This function does not handle some cases such as empty spaces between the fields. We have created a function namedcleanup_input_strwhich will modify the input string to remove all the unnecessary spaces and then call the inbuiltParseDateTimefunction.ParseDatetimesplits the input string to different fields which then will be decoded in native function namedDecodeDateTime. ButDecodeDatetimewill not handle some cases such as accepting:as a separator for seconds and milliseconds. These additional cases were handled in a different function namedtsql_decode_datetime_fields.Issues Resolved
BABEL-4328 Signed-off-by: Sai Rohan Basa bsrohan@amazon.com
Test Scenarios Covered
Use case based - YES
Boundary conditions - YES
Arbitrary inputs - YES
Negative test cases - YES
Minor version upgrade tests - YES
Major version upgrade tests - YES
Performance tests - N/A
Tooling impact - N/A
Client tests - N/A
Check List
By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 and PostgreSQL licenses, and grant any person obtaining a copy of the contribution permission to relicense all or a portion of my contribution to the PostgreSQL License solely to contribute all or a portion of my contribution to the PostgreSQL open source project.
For more information on following Developer Certificate of Origin and signing off your commits, please check here.