babelfish-for-postgresql
[Question]: select CAST(0x0000A47F000F896A AS DateTime);
Contact Details
What's the question?
I have a script of a sqlserver application for init database, with a lot of:
CAST(0x0000A47F000F896A AS DateTime);
I am trying to create a function such as the function in babelfish_extensions/contrib/babelfishpg_common/sql/datetime.sql, maybe someone can help to see how to deal with it ? or get me some suggestion
Relevant log output or information
1> select CAST(0x0000A47F000F896A AS DateTime);
2> go
Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
cannot cast type varbinary to datetime
Code of Conduct
- [X] I agree to follow this project's Code of Conduct
What is the use case for specifying a hex value here? Why is it preferred over just specifying the actual datetime value?
This StackOverflow question shows the logic for a conversion like this in C#:
https://stackoverflow.com/questions/63940870/convert-hex-value-from-sql-server-to-datetime-in-c-sharp
I understand the underlying logic - but my question is for what purpose would you have a SQL-based application want to convert a hex constant to a datetime? It seems a rather unusual thing to do.
Hi @suprio-amzn & @robverschoor , I just came across a customer database that had over over 1,200 instances of using a combination of converting the a datetime value to varbinary(8) and adding to it an attribute to use for SET CONTEXT_INFO. They did something like this for auditing transactions with triggers:
CREATE PROCEDURE dbo.example (
@audittime DATETIME
, @auditprofileguid UNIQUEIDENTIFIER
)
AS
BEGIN
DECLARE @context_info VARBINARY(128) =
CONVERT(VARBINARY(8), @audittime)
+ COALESCE( CONVERT(VARBINARY(16), @auditprofileguid), CONVERT(VARBINARY(16), '') );
SET CONTEXT_INFO @context_info;
-- rest of SP code that performs an insert, update, delete action on tables
END
-- Related trigger for tables updated from SP
CREATE TRIGGER "acl_auditing_trigger"
ON "acl" FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON;
DECLARE @audittime DATETIME = (CONVERT(DATETIME, SUBSTRING(CONTEXT_INFO(), 1, 8)))
DECLARE @auditprofileguid UNIQUEIDENTIFIER =
(CONVERT(UNIQUEIDENTIFIER, SUBSTRING(CONTEXT_INFO(), 9, 16)))
-- rest of trigger code
END
I noticed that there is a new check in from @R4hul04 - https://github.com/babelfish-for-postgresql/babelfish_extensions/commit/4bae2206d966debdbce756c7a6cf562bbd032eee that looks like it may help the scenario. It looks like it only deals with converting strings to binary and varbinary, but doesn't address datetime scenarios like the customer scenario above.
I also noticed that there were no changes to SUBSTRING() which incorrectly is character based and not byte based like SQL Server. For example, the SUBSTRING(CONTEXT_INFO(), 1, 8) expression returns 8 characters 0x0000b1 and not 8 bytes like SQL Server 0x0000B13C00EF291A. See below for a potential fix.
I'm able to workaround the customer scenarios and hope you can take this example code and create an implementation for Babelfish. . The datetime conversion is tricky with the epoch as '1900-01-01', an adjustment in '2000-01-01' for Y2K, and Noon being special - see below. Note: this is only for datetime. I used Amazon Bedrock with Anthropic Sonnet 3.5 with the Text chat playground with Temperature, K, and P values set to 0 to build out the code over several iterations. I don't know how different smalldatetime, date, time, datetime2, and other date/time types work. I was addressing the customer scenario.
CREATE OR REPLACE FUNCTION sys.datetime_to_binary(arg sys.datetime)
RETURNS sys.bbf_varbinary AS $$
DECLARE
sql_server_epoch timestamp := '1900-01-01 00:00:00';
days bigint;
time_part bigint;
hex_str text;
total_ms bigint;
second_whole bigint;
second_frac bigint;
BEGIN
-- Calculate days since 1900-01-01
IF arg::timestamp < sql_server_epoch THEN
days := -53690::bigint +
(EXTRACT(EPOCH FROM (arg::timestamp - '1753-01-01'::timestamp))::bigint / 86400::bigint)::bigint;
ELSE
days := (EXTRACT(EPOCH FROM (arg::timestamp - sql_server_epoch))::bigint / 86400::bigint)::bigint;
END IF;
-- Calculate time part (300 increments per second)
IF EXTRACT(HOUR FROM arg::timestamp) = 12 AND
EXTRACT(MINUTE FROM arg::timestamp) = 0 AND
EXTRACT(SECOND FROM arg::timestamp)::bigint = 0 THEN
time_part := x'00C5C100'::bigint;
ELSE
-- Break down time calculation to avoid numeric type issues
second_whole := EXTRACT(SECOND FROM arg::timestamp)::bigint;
second_frac := ((EXTRACT(SECOND FROM arg::timestamp)::float - second_whole::bigint) * 1000::bigint)::bigint;
total_ms := (EXTRACT(HOUR FROM arg::timestamp)::bigint * 3600000::bigint) +
(EXTRACT(MINUTE FROM arg::timestamp)::bigint * 60000::bigint) +
(second_whole * 1000::bigint) +
second_frac;
-- Convert to SQL Server's 300ths of a second ticks
time_part := (total_ms * 3::bigint) / 10::bigint;
END IF;
-- Convert to 8-byte hex
IF days < 0 THEN
hex_str := lpad(to_hex((days & x'FFFFFFFF'::bigint)::bigint), 8, '0');
ELSE
hex_str := lpad(to_hex(days), 8, '0');
END IF;
hex_str := hex_str || lpad(to_hex(time_part), 8, '0');
RETURN decode(hex_str, 'hex')::sys.bbf_varbinary;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION sys.binary_to_datetime(arg sys.bbf_varbinary)
RETURNS sys.datetime AS $$
DECLARE
sql_server_epoch timestamp := '1900-01-01 00:00:00';
hex_str text;
days bigint;
time_part bigint;
day_interval interval;
time_interval interval;
day_value bigint;
ms_value bigint;
BEGIN
hex_str := encode(arg::bytea, 'hex');
-- Extract days and time parts
days := ('x' || substr(hex_str, 1, 8))::bit(32)::integer;
time_part := ('x' || substr(hex_str, 9, 8))::bit(32)::integer;
-- Convert from SQL Server's 300ths of a second ticks back to milliseconds
-- Multiply by 10 first then divide by 3 to maintain precision
ms_value := (time_part::bigint * 10::bigint) / 3::bigint;
time_interval := ((ms_value::double precision / 1000.0) * interval '1 second');
-- Convert days to interval
IF days < 0 THEN
-- For dates before 1900, use the correct offsets for SQL Server's representation
day_value := (days::bigint & x'FFFFFFFF'::bigint)::bigint;
RETURN ('1753-01-01'::timestamp +
((day_value - x'FFFF2E46'::bigint) * interval '1 day') +
time_interval)::sys.datetime;
ELSE
-- Post 1900 dates work as before
day_interval := days * interval '1 day';
RETURN (sql_server_epoch + day_interval + time_interval)::sys.datetime;
END IF;
END;
$$ LANGUAGE plpgsql;
Key Implementation Details for SQL Server datetime Binary Format:
Source: Claude.ai
Binary Structure:
8 bytes total (varbinary/binary(8)) First 4 bytes: Days since base date Last 4 bytes: Time of day in 300ths of a second (3.33ms increments)
Date Handling:
- Base date (epoch): January 1, 1900
- Valid range: January 1, 1753 to December 31, 9999
- Pre-1900 dates use negative day counts with special offset
- Days stored as 32-bit integer
Time Handling:
- Stored in 300ths of a second (3.33ms precision)
- Special case: 12:00:00 noon = 0x00C5C100
- Milliseconds must be rounded to nearest 3.33ms increment
Edge Cases and Special Considerations:
- Pre-1900 dates require offset calculation from 1753-01-01
- Two's complement handling for negative days
- Noon has special binary representation
- Millisecond precision needs careful handling for rounding
- Dates before 1753-01-01 should error
- Dates after 9999-12-31 should error
- NULL input should return NULL
Binary Format Examples:
1753-01-01 00:00:00.000 = 0xFFFF2E4600000000
1900-01-01 00:00:00.000 = 0x0000000000000000
2000-01-01 00:00:00.000 = 0x00008EAC00000000
Precision Considerations:
- SQL Server datetime rounds to .000, .003, or .007 seconds
- Binary conversions must maintain this rounding behavior
- Time component must align with 3.33ms intervals
This implementation provides a way to:
Convert datetime to 8-byte binary matching SQL Server's format Convert 8-byte binary back to datetime preserving precision Handle the full range of valid SQL Server datetime values Maintain proper rounding behavior for milliseconds Handle special cases like noon and pre-1900 dates
Usage Example:
-- Convert datetime to binary
SELECT sys.datetime_to_binary('2024-03-23 14:30:45.420');
-- Convert binary back to datetime
SELECT sys.binary_to_datetime(0x0000B0EA00C5C100);
Date experiment
Date data type works with a cast of the returned value.
DECLARE @date date = '2024-03-23 14:30:45.420';
select @date, sys.datetime_to_binary(@date)
, sys.binary_to_datetime(sys.datetime_to_binary(@date))
, cast(sys.binary_to_datetime(sys.datetime_to_binary(@date)) as date);
Returns
| @date | datetime_to_binary | binary_to_datetime | binary_to_datetime |
|---|---|---|---|
| 2024-03-23 | 0x0000B13C00000000 | 2024-03-23 00:00:00.000 | 2024-03-23 |
Small date time works, you just need to cast the final result.
DECLARE @date smalldatetime = '2024-03-23 14:30:45.420';
select @date
, sys.datetime_to_binary(@date)
, cast(sys.binary_to_datetime(sys.datetime_to_binary(@date)) as smalldatetime)
Returns
| @date | datetime_to_binary | binary_to_datetime |
|---|---|---|
| 2024-03-23 14:31:00 | 0x0000B13C00EF3A30 | 2024-03-23 14:31:00 |
DATETIME2() data type works, but only with 3 digits of precision. It likely needs more bytes for the greater precision.
DECLARE @date datetime2(6) = '2024-03-23 14:30:45.423409';
select @date
, sys.datetime_to_binary(@date)
, cast(sys.binary_to_datetime(sys.datetime_to_binary(@date)) as datetime2(6))
Returns
| @date | datetime_to_binary | binary_to_datetime |
|---|---|---|
| 2024-03-23 14:30:45.423409 | 0x0000B13C00EF291A | 2024-03-23 14:30:45.420000 |
Time data type requires the use of the second set of four bytes and requires a new function to handle the time in the same way sys.binary_to_datetime
Problem with + operator with varbinary
SQL Server behavior
DECLARE @var1 varbinary(4) = 0x1234
DECLARE @var2 varbinary(4) = 0x5678
select @var1 + @var2 -- Results in 0x12345678
Babelfish behavior
DECLARE @var1 varbinary(4) = 0x1234
DECLARE @var2 varbinary(4) = 0x5678
SELECT concat(@var1, @var2) -- Results in 0x12340x5678 not 0x12345678
select @var1 + @var2 -- Results in 26796 not 0x12345678 - this is the sum of the two values and expressed as an integer!
select cast( @var1 + @var2 as varbinary(4)); -- Results in 0x000068AC notice the length is fixed, using varbinary(2) shows 0x68AC
As you can see, concat is messed up and is not the same as +. This is similar behavior as PostgreSQL:
SELECT '\x1234' || '\x5678'; -- Results in \x1234\x5678
SELECT '\x1234'::bytea || '\x5678'::bytea; -- Results in \x12345678
Solving concat and + for varbinary data in Babelfish
Here's a detailed explanation of the varbinary concatenation function for Babelfish:
Function Purpose:
This function implements SQL Server's varbinary concatenation behavior (using the + operator) in PostgreSQL. In SQL Server, 0x1234 + 0x5678 results in 0x12345678 - a direct concatenation of the hex values.
Key Components:
-
Input Arguments:
- Both arguments are
sys.bbf_varbinarytype (Babelfish's implementation of SQL Server's varbinary) arg1represents the left side of the concatenationarg2represents the right side of the concatenation
- Both arguments are
-
Type Conversions:
encode(arg1::bytea, 'hex')- First converts bbf_varbinary to PostgreSQL's native bytea
- Then converts bytea to a hex string representation
-
String Processing:
substring(...from 1)- Removes any leading characters from the hex representation
- Ensures clean hex strings for concatenation
-
Concatenation:
hex_str1 || hex_str2- Uses PostgreSQL's string concatenation operator
- Directly joins the hex strings
-
Final Conversion:
decode(..., 'hex')::sys.bbf_varbinary- Converts concatenated hex string back to binary
- Casts to bbf_varbinary for T-SQL compatibility
Example Usage:
-- In SQL Server:
DECLARE @var1 varbinary(4) = 0x1234
DECLARE @var2 varbinary(4) = 0x5678
SELECT @var1 + @var2 -- Results in 0x12345678
-- In Babelfish:
DECLARE @var1 varbinary(4) = 0x1234
DECLARE @var2 varbinary(4) = 0x5678
SELECT sys.varbinary_concat(@var1, @var2) -- Also results in 0x12345678
Why It Works with Babelfish:
- Maintains SQL Server's binary concatenation semantics
- Handles type conversions between Babelfish and PostgreSQL types
- Uses PostgreSQL's native functions (encode, decode) for binary processing
- Preserves the exact byte sequence without adding/removing bytes
- Returns the correct Babelfish type (sys.bbf_varbinary)
Adding the sys.+ operator for varbinary
This function could be used as the implementation for a binary concatenation operator in Babelfish:
CREATE OPERATOR sys.+ (
LEFTARG = sys.bbf_varbinary,
RIGHTARG = sys.bbf_varbinary,
PROCEDURE = sys.varbinary_concat
);
Now in Babelfish, the following works as expected
DECLARE @var1 varbinary(4) = 0x1234
DECLARE @var2 varbinary(4) = 0x5678
select @var1 + @var2 -- Results in 0x12345678
Here is the customer example that mostly works for the conversion to varbinary and + operator
-- Customer example with variable value display
DECLARE @auditprofileguid UNIQUEIDENTIFIER = NEWID();
DECLARE @vb_auditprofileguid VARBINARY(16) = CONVERT(varbinary(16), @auditprofileguid);
DECLARE @audittime DATETIME = '2024-03-23 14:30:45.420';
DECLARE @vb_audittime VARBINARY(8) = sys.datetime_to_binary(@audittime);
DECLARE @context_info VARBINARY(128) = @vb_audittime + @vb_auditprofileguid;
SET CONTEXT_INFO @context_info;
-- Display all variable values in a result set
SELECT '@auditprofileguid' AS VariableName, cast(@auditprofileguid as nvarchar(500)) AS VariableValue, 'UNIQUEIDENTIFIER' AS DataType;
SELECT '@vb_auditprofileguid' AS VariableName, @vb_auditprofileguid AS VariableValue, 'VARBINARY(16)' AS DataType;
SELECT '@audittime' AS VariableName, @audittime AS VariableValue, 'DATETIME' AS DataType;
SELECT '@vb_audittime' AS VariableName, @vb_audittime AS VariableValue, 'VARBINARY(8)' AS DataType;
SELECT '@context_info' AS VariableName, @context_info AS VariableValue, 'VARBINARY(128)' AS DataType;
SELECT 'CONTEXT_INFO()' AS VariableName, CONTEXT_INFO() AS VariableValue, 'VARBINARY(128)' AS DataType;
Returns
| VariableName | VariableValue | DataType |
|---|---|---|
| @auditprofileguid | FFF2EB80-8893-4C39-92FB-9AED1775EB13 | UNIQUEIDENTIFIER |
| @vb_auditprofileguid | 0x80EBF2FF9388394C92FB9AED1775EB13 | VARBINARY(16) |
| @audittime | 2024-03-23 14:30:45.420 | DATETIME |
| @vb_audittime | 0x0000B13C00EF291A | VARBINARY(8) |
| @context_info | 0x0000B13C00EF291A 80EBF2FF9388394C92FB9AED1775EB13 | VARBINARY(128) |
| space added manually^ | ||
| CONTEXT_INFO() | 0x0000B13C00EF291A80EBF2FF9388394C92FB9AED1775EB130000... | VARBINARY(128) |
Getting the data out is an issue now with SUBSTRING()
In T-SQL, SUBSTRING() treats a position as a hex byte and doesn't count the leading 0x value.
-- SQL Server bringing back the datetime
SELECT SUBSTRING(CONTEXT_INFO(), 1, 8); -- Returns: 0x0000B13C00EF291A
However, in Babelfish
SELECT SUBSTRING(CONTEXT_INFO(), 1, 8); -- Returns: 0x0000b1 It's character based and not byte based! And they are
SUBSTRING for varbinary
Here's a detailed explanation of the sys.binary_substring function for the AWS Babelfish development team to start from.
CREATE OR REPLACE FUNCTION sys.binary_substring(
arg sys.bbf_varbinary, -- Input is Babelfish's binary type
start_pos integer, -- 1-based starting position (SQL Server style)
length integer) -- Number of bytes to extract
RETURNS sys.bbf_varbinary -- Returns Babelfish's binary type
LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
AS $$
BEGIN
-- The function performs these steps:
-- 1. arg::bytea - Converts bbf_varbinary to PostgreSQL bytea
-- 2. ENCODE(..., 'hex') - Converts bytea to hex string representation
-- 3. SUBSTRING(...) extracts the requested bytes:
-- - (start_pos - 1) * 2 - Converts 1-based to 0-based indexing and multiplies by 2 since each byte is 2 hex chars
-- - + 1 - Adjusts to 1-based for PostgreSQL substring
-- - length * 2 - Multiplies requested length by 2 to get correct number of hex chars
-- 4. decode(..., 'hex') - Converts hex string back to bytea
-- 5. ::sys.bbf_varbinary - Casts back to Babelfish binary type
RETURN decode(
SUBSTRING(
ENCODE(arg::bytea, 'hex')::text,
(start_pos - 1) * 2 + 1,
length * 2
)
, 'hex')::sys.bbf_varbinary
;
END;
$$;
Key implementation details:
-
Type Handling:
- Input: Uses sys.bbf_varbinary (Babelfish's binary type)
- Internal: Converts to bytea for PostgreSQL operations
- Output: Returns sys.bbf_varbinary for SQL Server compatibility
-
Position Handling:
- SQL Server uses 1-based indexing for SUBSTRING
- PostgreSQL SUBSTRING also uses 1-based indexing
- Each byte requires 2 hex characters in string representation
-
Binary Processing:
- Preserves byte alignment by working with hex representation
- Maintains binary data integrity throughout conversion chain
- No loss of binary data during string conversion phases
-
SQL Server Compatibility:
-- SQL Server example: DECLARE @var3 varbinary(4) = 0x12345678 SELECT substring(@var3, 1, 2) -- Returns: 0x1234 SELECT substring(@var3, 3, 2) -- Returns: 0x5678 -
Example Operation Flow:
Input: 0x12345678, start_pos=1, length=2 1. bytea conversion: \x12345678 2. hex encoding: "12345678" 3. substring calculation: - start: (1-1)*2 + 1 = 1 - length: 2*2 = 4 4. substring result: "1234" 5. hex decode: \x1234 6. final result: 0x1234
This implementation ensures:
- Correct byte alignment
- SQL Server compatible behavior
- Proper binary data handling
- Efficient processing
- Data integrity throughout conversions
Test cases
-- Setup test variables
DECLARE @test_passed bit;
DECLARE @error_message nvarchar(1000);
DECLARE @result_message nvarchar(1000) = '';
BEGIN TRY
-- Test Case 1: Basic binary substring
DECLARE @var1 varbinary(4) = 0x12345678;
IF (sys.binary_substring(@var1, 1, 2) <> 0x1234)
BEGIN
SET @error_message = 'Test 1 Failed: Basic binary substring first half. Expected 0x1234, Got ' +
CONVERT(varchar(20), sys.binary_substring(@var1, 1, 2), 2);
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 1 Passed: Basic substring first half' + CHAR(13);
-- Test Case 2: Middle substring
IF (sys.binary_substring(@var1, 2, 2) <> 0x3456)
BEGIN
SET @error_message = 'Test 2 Failed: Middle substring. Expected 0x3456, Got ' +
CONVERT(varchar(20), sys.binary_substring(@var1, 2, 2), 2);
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 2 Passed: Middle substring' + CHAR(13);
-- Test Case 3: Last part substring
IF (sys.binary_substring(@var1, 3, 2) <> 0x5678)
BEGIN
SET @error_message = 'Test 3 Failed: Last part substring. Expected 0x5678, Got ' +
CONVERT(varchar(20), sys.binary_substring(@var1, 3, 2), 2);
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 3 Passed: Last part substring' + CHAR(13);
-- Test Case 4: Single byte extraction
IF (sys.binary_substring(@var1, 2, 1) <> 0x34)
BEGIN
SET @error_message = 'Test 4 Failed: Single byte extraction. Expected 0x34, Got ' +
CONVERT(varchar(20), sys.binary_substring(@var1, 2, 1), 2);
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 4 Passed: Single byte extraction' + CHAR(13);
-- Test Case 5: Zero length
IF (DATALENGTH(sys.binary_substring(@var1, 1, 0)) <> 0)
BEGIN
SET @error_message = 'Test 5 Failed: Zero length. Expected empty binary';
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 5 Passed: Zero length substring' + CHAR(13);
-- Test Case 6: Empty binary input
DECLARE @empty_var varbinary(4) = 0x;
IF (DATALENGTH(sys.binary_substring(@empty_var, 1, 2)) <> 0)
BEGIN
SET @error_message = 'Test 6 Failed: Empty binary input. Expected empty binary';
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 6 Passed: Empty binary input' + CHAR(13);
-- Test Case 7: Start position beyond length
IF (DATALENGTH(sys.binary_substring(@var1, 5, 1)) <> 0)
BEGIN
SET @error_message = 'Test 7 Failed: Start beyond length. Expected empty binary';
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 7 Passed: Start position beyond length' + CHAR(13);
-- Test Case 8: Length beyond available bytes
IF (sys.binary_substring(@var1, 3, 4) <> 0x5678)
BEGIN
SET @error_message = 'Test 8 Failed: Length beyond available. Expected 0x5678, Got ' +
CONVERT(varchar(20), sys.binary_substring(@var1, 3, 4), 2);
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 8 Passed: Length beyond available bytes' + CHAR(13);
-- Test Case 9: Large varbinary
DECLARE @large_var varbinary(8000) = 0xFFFFFFFFFFFFFFFF;
IF (sys.binary_substring(@large_var, 1, 8) <> 0xFFFFFFFFFFFFFFFF)
BEGIN
SET @error_message = 'Test 9 Failed: Large varbinary. Expected 0xFFFFFFFFFFFFFFFF';
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 9 Passed: Large varbinary' + CHAR(13);
-- Test Case 10: Odd number of hex digits
DECLARE @odd_var varbinary(3) = 0x123456;
IF (sys.binary_substring(@odd_var, 2, 1) <> 0x34)
BEGIN
SET @error_message = 'Test 10 Failed: Odd number of hex digits. Expected 0x34, Got ' +
CONVERT(varchar(20), sys.binary_substring(@odd_var, 2, 1), 2);
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 10 Passed: Odd number of hex digits' + CHAR(13);
-- Test Case 11: NULL input
IF (sys.binary_substring(CAST(NULL AS varbinary(4)), 1, 2) IS NOT NULL)
BEGIN
SET @error_message = 'Test 11 Failed: NULL input should return NULL';
RAISERROR(@error_message, 16, 1);
END
SET @result_message += 'Test 11 Passed: NULL input' + CHAR(13);
-- Print final results
PRINT 'All tests completed successfully!';
PRINT @result_message;
-- Display test results in table format
SELECT
'First Half (1,2)' as TestCase,
0x1234 as Expected,
sys.binary_substring(0x12345678, 1, 2) as Actual
UNION ALL
SELECT
'Last Half (3,2)',
0x5678,
sys.binary_substring(0x12345678, 3, 2)
UNION ALL
SELECT
'Middle Bytes (2,2)',
0x3456,
sys.binary_substring(0x12345678, 2, 2);
END TRY
BEGIN CATCH
-- Error handling
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage,
ERROR_SEVERITY() as ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_LINE() as ErrorLine;
PRINT 'Test suite failed! See error details above.';
PRINT 'Completed tests up to error:';
PRINT @result_message;
END CATCH;
Results Messages
All tests completed successfully!
Test 1 Passed: Basic substring first half
Test 2 Passed: Middle substring
Test 3 Passed: Last part substring
Test 4 Passed: Single byte extraction
Test 5 Passed: Zero length substring
Test 6 Passed: Empty binary input
Test 7 Passed: Start position beyond length
Test 8 Passed: Length beyond available bytes
Test 9 Passed: Large varbinary
Test 10 Passed: Odd number of hex digits
Test 11 Passed: NULL input
Grid results
| TestCase | Expected | Actual |
|---|---|---|
| First Half (1,2) | 0x1234 | 0x1234 |
| Last Half (3,2) | 0x5678 | 0x5678 |
| Middle Bytes (2,2) | 0x3456 | 0x3456 |
Back to the customer scenario
SELECT sys.binary_substring(CONTEXT_INFO(), 1, 8) -- Returns: 0x0000B13C00EF291A
, cast(sys.binary_to_datetime(sys.binary_substring(CONTEXT_INFO(), 1, 8)) as varchar(100))
UNION ALL
SELECT sys.binary_substring(CONTEXT_INFO(), 9, 16)
, CAST(CONVERT(UNIQUEIDENTIFIER, sys.binary_substring(CONTEXT_INFO(), 9, 16)) as varchar(100))
;
Returns the expected results
| binary_substring | binary_to_datetime |
|---|---|
| 0x0000B13C00EF291A | 2024-03-23 14:30:45.42 |
| 0x80EBF2FF9388394C92FB9AED1775EB13 | FFF2EB80-8893-4C39-92FB-9AED1775EB13 |
I hope you made it this far. Thanks, Bill
We have supported varbinary to datetime cast in Babelfish.
1> select CAST(0x0000A47F000F896A AS DateTime);
2> go
datetime
-----------------------
2015-04-19 00:56:34.060
(1 rows affected)
from BBF version:
Babelfish for PostgreSQL with SQL Server Compatibility - 12.0.2000.8
Sep 1 2025 17:48:56
Copyright (c) Amazon Web Services
PostgreSQL 16.9 on x86_64-pc-linux-gnu (Babelfish 4.8.0)
We have also supported sys.+ operator (concat) for varbinary in babaelfish.
1> declare @random varbinary(5) = 0x4142434445
2> declare @result varbinary(max) = 0x
3> select cast(@result + @random as varbinary(10))
4> go
varbinary
----------------------
0x4142434445
1> select cast(0x123 as varbinary(5)) + cast(0x123 as varbinary(8))
2> go
----------
0x01230123
(1 rows affected)