Timestamps, numbers, and names

Three rules determine whether a value arrives the way it was meant: values are bound by the type of their column, not as text; a timestamp without a time zone appears over OData with a Z; and names containing characters that OData doesn't allow are encoded. This page explains all three in one place, and the reference pages link here.

• Dates, timestamps, amounts, and floating-point values arrive exactly, regardless of the language setting.
• Over OData, reading and writing timestamps are symmetric.
• Every name that SQL Server accepts can be reached, as long as its encoded form is no longer than 128 characters.

Values are bound by column type, not as text. A date in the request body reaches SQL Server as a date. If it were sent as text, SQL Server would interpret it according to the connection's language setting, and with German settings 2023-06-01 would become January 6—and be stored that way for good. This leads to the following rules:

• A date is accepted only in ISO format; 01.06.2023 is ambiguous.
• A timestamp with a time zone for a column without one is refused on the native route and converted over OData (see Timestamps without a time zone).
• An amount is bound exactly, including in exponent notation: for a decimal column, a JSON value such as 1.5E2 is parsed from its digits and keeps every one of them. This holds for up to 38 digits, as many as decimal(38, s) holds, when writing and when reading; a longer number is refused for a decimal column.
• A Boolean value for a text column is refused.
• A floating-point value arrives bit for bit. It's parsed from its digits, not converted from decimal, a conversion that doesn't round correctly.

A timestamp is bound as the type of its column, both when writing and in a filter: as datetime2 for a datetime2 column, as date for a date column. If the type were inferred from the value alone, every timestamp would be a datetime—rounded to 1/300 of a second and limited to the year 1753 and later. Because of this rule, a filter matches a stored value with seven fractional digits, and a written value keeps all seven digits, even in the year 1.

A null for a binary column is bound as a binary value. Without a type, null would reach SQL Server as nvarchar, and SQL Server doesn't implicitly convert nvarchar to binary, varbinary, or image—not even a null. An insert would then fail with error 257. Access leaves out fields that weren't set, but the ADO.NET CommandBuilder lists every column, including a varbinary column set to null.

Put a date in a filter as a timestamp, not as a string. On the OData interface, ge 2023-06-01T00:00:00Z (the OData 4 literal) and ge datetime'2023-06-01T00:00:00' (the OData 3 literal that Access and Power Query write) bind a timestamp; ge '2023-06-01' binds text. For a datetime or smalldatetime column, SQL Server interprets that text according to the connection's language setting: against a database with German settings, '2023-06-01' becomes January 6—no error, no warning, just a different set of rows.

A decimal value is bound exactly, not as a floating-point number. SQL Server gives floating point a higher precedence than decimal (Microsoft: Data type precedence). A floating-point parameter compared with a decimal or money column therefore converts the column: its index can no longer be used, and the comparison runs against an approximation of the stored value. Two different decimal values can map to the same floating-point value: DecimalField eq 123456789012.345678 matches one row, while the same filter with …678d matches two. Floating point is used only where the literal calls for it—an f or d suffix, exponent notation, or a value outside the exact range.

A refused literal costs more than it seems. A spreadsheet or reporting client doesn't report the 400; it drops the condition and reads the whole object instead. A targeted query turns into a full table read, which can hit the time limit on a large table. That's why the protocol's literals are part of the service contract, not a convenience.

A timestamp without a time zone appears with a Z. OData v4 has no type for a timestamp without a time zone. The service writes the stored wall-clock time and appends Z. The Z is dictated by the protocol; it says nothing about the stored time, because the database doesn't know which time zone its values came from. To match, a filter value with a time zone is converted to Coordinated Universal Time (UTC) for such a column: ge 2023-01-01T00:00:00+01:00 compares against December 31, 2022, 11:00 PM. The two rules only ever change together.

Over OData, a timestamp with a time zone is converted to UTC when it's written. The read side appends a Z for a column without a time zone, so a client that reads a row, changes it, and sends it back includes that Z; the service strips it off again. Any other offset is converted the same way: 17:00+02:00 is stored as 15:00—the same point in time that a filter with a time zone refers to. The native route (/api/v1/cf/…), by contrast, refuses a timestamp with a time zone for such a column, because it has no such convention.

Not every type can be compared or sorted. SQL Server doesn't compare or sort text, ntext, image, xml, geography, and geometry. It compares a sql_variant by its base type: a value holding the number 42 isn't equal to the text '42', and the filter would silently find nothing. Over OData, the service therefore rejects a comparison in $filter and a sort in $orderby on such a column with 400 and names the column; the check for a missing value (eq null) and text search stay allowed. hierarchyid is ordered by its bytes and can be compared and sorted, with a binary value in hexadecimal notation: binary'5B40'. The native route passes a comparison to SQL Server unchanged; a comparison on sql_variant there finds a value only if it's stored in the same base type.

vector (SQL Server 2025) reports varbinary as its system type but refuses conversion to varbinary(max). The catalog therefore lists it under its own name. Listed as varbinary, it would be offered as a binary value without being one, and a row stamp over the table would make every read fail.

Inside square brackets, SQL Server accepts almost any character in a name: [F&E], [Ein/Aus], [Prüf-Kennung], [Pos. Nr.]. OData allows only letters, digits, and the underscore in a name, up to 128 characters (SimpleIdentifier in OData CSDL XML 4.01, section „SimpleIdentifier“). The service therefore encodes such names the same way SQL Server does in FOR XML and SharePoint does for field names: a character that isn't allowed in its position becomes _x, followed by its code as four uppercase hexadecimal digits and _; outside Unicode's Basic Multilingual Plane, the code has eight digits.

Only the catalog's spelling ever goes into a statement: the service looks up the encoded name in the catalog and replaces it with the stored name before writing it into the statement—in brackets, with any ] doubled. The ODBC driver translates names back for Access, so F&E shows up as F&E there.

How a name gets into a statement safely. Names with umlauts and accents are allowed, even though databases compare identifiers under collations that treat accents differently; otherwise nobody could write to a table with a required column such as GeändertAm. Two steps protect this boundary: the name is looked up in the catalog that the service read from the database, and only the catalog's spelling goes into the statement, in brackets and with the closing bracket doubled. The comparison is ordinal—case is folded, accents aren't. An encoded name is compared with the catalog's encoded names, never with the stored ones; otherwise a column literally named F_x0026_E could be mistaken for F&E.

• HTTP: OData v4 (reference)
• A date filter returns the wrong rows