fix(mssql): read DATETIME and SMALLDATETIME as the engine's own text, and export them with a T by Aditya-XR · Pull Request #1639 · libredb/libredb-studio

Aditya-XR · 2026-10-10T05:28:51Z

Description

SQL Server DATETIME and SMALLDATETIME cells now read as the engine's own wall-clock text, the same way time, date and datetime2 have since #1178. A DATETIME next to a DATETIME2 holding the same reading used to render as 2026-10-04T12:34:56.123Z against 2026-10-04 12:34:56.123. Both now show 2026-10-04 12:34:56.123.

Two commits:

fix(mssql): the display fix the issue asks for. datetime and smalldatetime are added to ZONELESS_VALUE_DECLARATIONS. The driver sends no scale for these two types (tedious metadata-parser.js reads one only for time, datetime2 and datetimeoffset), so the fraction digits come from the type through a new FIXED_FRACTION_DIGITS map. datetime gets 3 digits, and its 1/300 s ticks already arrive rounded to the millisecond the engine prints (.000/.003/.007). smalldatetime gets 0 and always shows :00 seconds. datetimeoffset still stays an instant.
fix(export): keeps the SQL INSERT export replaying correctly. I mentioned this in my claim comment. Unlike datetime2 and date, DATETIME and SMALLDATETIME read yyyy-mm-dd hh:mi:ss through the session's DATEFORMAT. Before commit 1, the export wrote these cells as the Date's ISO text with a Z, which replays the same under every language. After commit 1 the cell is the engine text with a space, and under a dmy language (british, Deutsch, Français) '2026-10-04 12:34:56.123' replays as April 10th, and '2026-10-25 23:59:59.997' is refused as out of range. So mssqlLiteral now writes that exact text with its space as a T (N'2026-10-04T12:34:56.123'), the ISO 8601 form every DATEFORMAT reads the same. Only a cell declared datetime/smalldatetime whose text is exactly the provider's form takes this path. Everything else goes to the generic writer as before, and datetime2/date text is not touched.

I kept them as separate commits so the export change can be dropped or split out if you'd rather handle it separately.

Live measurement

SQL Server 2022 CU27 (16.0.4295.3), mcr.microsoft.com/mssql/server:2022-latest, mssql 12.7.2 / tedious 20.3.0, Bun 1.4.2.

tests/live/mssql-zoneless-values.ts now also guards dt DATETIME, dtTick DATETIME and sdt SMALLDATETIME against the engine's own CONVERT text (styles 121 and 120, because their default style prints Sep 1 2026 10:30AM):

declared / inserted	engine's own text	`main`	this PR
`DATETIME` `2026-09-01T10:30:00.123`	`2026-09-01 10:30:00.123`	`2026-09-01T10:30:00.123Z`	`2026-09-01 10:30:00.123`
`DATETIME` `2026-09-01T23:59:59.998`	`2026-09-01 23:59:59.997`	`2026-09-01T23:59:59.997Z`	`2026-09-01 23:59:59.997`
`SMALLDATETIME` `2026-09-01T10:30:29.999`	`2026-09-01 10:31:00`	`2026-09-01T10:31:00.000Z`	`2026-09-01 10:31:00`

The guard fails on main and passes with this PR. The existing time/date/datetime2 columns and the datetimeoffset control pass unchanged.

Export replay (a throwaway script, not committed): two rows read through the provider, sent through JSON.parse(JSON.stringify(...)) the way POST /api/db/query returns them, exported with buildResultExport("sql-insert", …), replayed into an empty table under SET LANGUAGE, and compared on the server:

Rows: DATETIME, SMALLDATETIME, DATETIME2(3) = (2026-10-04 12:34:56.123, 2026-10-04 12:35:00, 2026-10-04 12:34:56.123) and (2026-10-25 23:59:59.997, 2026-10-25 08:15:00, 2026-10-25 23:59:59.997).

export written as	replay under `us_english`	replay under `british`
`main`: `N'2026-10-04T12:34:56.123Z'`	both rows equal	both rows equal
commit 1 alone: `N'2026-10-04 12:34:56.123'`	both rows equal	row 1: `dt` and `sdt` stored as 2026-04-10; row 2 refused: `The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.`
this PR: `N'2026-10-04T12:34:56.123'` (the `DATETIME2` cell stays `N'2026-10-04 12:34:56.123'`)	both rows equal	both rows equal

Type of Change

Bug fix (non-breaking change which fixes an issue)
New feature (non-breaking change which adds functionality)
Breaking change (fix or feature that would cause existing functionality to not work as expected)
Documentation update
Code refactoring
Performance improvement
Test addition or update

Related Issue

Closes #1452

Changes Made

src/lib/db/providers/sql/mssql.ts: datetime and smalldatetime added to ZONELESS_VALUE_DECLARATIONS, new FIXED_FRACTION_DIGITS map, and convertZonelessValues uses the type's digits when the column has no scale. Docblock updated.
src/lib/db/providers/sql/column-types.ts: the MssqlColumnMetadata.scale docblock now says scale is absent for datetime and smalldatetime too.
src/lib/export/typed-literals.ts: mssqlLiteral writes a datetime/smalldatetime cell that matches MSSQL_DATETIME_TEXT with a T. The BIT handling is unchanged.
tests/integration/db/mssql-provider.test.ts: 3 tests in the SQL Server time values arrive as a date on 1970-01-01 #1132 zoneless block: datetime with 3 digits (incl. a whole second and a 1/300 s tick), smalldatetime to the minute, and the issue's own DATETIME beside DATETIME2 repro with datetimeoffset still an instant.
tests/unit/lib/export/typed-literals.test.ts: 2 tests. The T form for datetime/smalldatetime (incl. a padded mixed-case declaration). Everything else falls through: datetime2, an undeclared column, ISO text with Z, 7 fraction digits, no seconds, leading text, a Date.
tests/unit/lib/export/result-export.test.ts: 1 test, a full INSERT with datetime, smalldatetime and datetime2 side by side.
tests/live/mssql-zoneless-values.ts: dt, dtTick and sdt columns guarded against styles 121/120.
docs/providers/mssql.md: §5.3 bullet with the measurement table, §5.5 explains why the export writes the T, with the replay measurement, and §13.4 lists the new guarded types.

Testing

I have tested this locally
I have added/updated tests
All existing tests pass
5 of the 6 new tests fail on main. The 6th pins the fall-through cases, which main already leaves to the generic writer. Targeted: mssql-provider.test.ts 254 pass, typed-literals.test.ts 37 pass, result-export.test.ts 188 pass.
Linux (Ubuntu 24.04, WSL2, Node 24.21, Bun 1.4.2), CI's coverage command: bun tests/run-tests.ts --coverage --merge-into=coverage/lcov.info --jobs=8 gave 1133 files: 1133 passed | 44018 tests: 44002 pass, 16 skip, and bun run coverage:check gave check-coverage: OK — 121844/121844 lines (100.00%). 15 of the 16 skips name a tool this machine does not have (zip, 7z) or are the runner's own skip-path self-test. The 16th is a test.skip in agent-investigation.test.ts. Helm wasn't on that machine, so the chart tests ran separately after installing Helm 4.1.3 and helm dependency build charts/libredb-studio: LIBREDB_REQUIRE_HELM=1 over tests/unit/helm-chart-*.test.ts gave 17 files, 251 pass.
format, lint (0 errors, no warnings in the touched files), typecheck, knip, chart:check, channels:showcase:check, readme:check, security:check, build, build:lib and attw all pass.
Live: tests/live/mssql-zoneless-values.ts passes. With main's mssql.ts and typed-literals.ts it fails on exactly dt, dtTick and sdt. Results are in the tables above.

Test Environment

LibreDB Studio Version: 0.18.2 (rebased on main at aad0c73)
Browser: N/A (provider and export change)
OS: Ubuntu 24.04 (WSL2), Windows 11
Node.js/Bun Version: Node 24 / Bun 1.4.2
Database Type: SQL Server 2022 CU27 (16.0.4295.3), mssql 12.7.2 / tedious 20.3.0

Checklist

Additional Notes

The coverage-gate box is left for CI's Linux runner to confirm. It passed locally (see Testing).

libredb#1178 made time, date and datetime2 read as the engine's own text, because none of them holds a zone. datetime and smalldatetime are zoneless in the same way and were left out, so a DATETIME beside a DATETIME2 holding the same reading rendered as 2026-10-04T12:34:56.123Z against 2026-10-04 12:34:56.123. Both are added to ZONELESS_VALUE_DECLARATIONS. The driver sends no scale for them (tedious reads one only for time, datetime2 and datetimeoffset), so their fraction digits come from the type: three for datetime, whose 1/300 s ticks tedious already rounds to the millisecond the engine prints, and none for smalldatetime, which is stored to the minute. The live guard reads both against the engine's style 121 and 120 text, since their default CONVERT style prints "Sep 1 2026 10:30AM". Refs libredb#1452

The provider now reads datetime and smalldatetime as the engine's yyyy-mm-dd hh:mi:ss text. Unlike datetime2 and date, those two types read that form through the session's DATEFORMAT. Measured on SQL Server 2022 CU27: under british, Deutsch and Francais (dmy) '2026-10-04 12:34:56.123' replays as April 10th and '2026-10-25 23:59:59.997' is refused as out of range. The export used to write the Date's ISO text with a Z, which replays the same under every language, so writing the new text as it is would have broken replay on those servers. mssqlLiteral now writes that text with its space as a T, the ISO 8601 form every DATEFORMAT reads the same. Any other cell falls through to the generic writer as before, and datetime2 and date text is left as it is. Refs libredb#1452

Copilot

Copilot was unable to review this pull request because the user who requested the review has reached their quota limit.

codecov · 2026-10-10T05:34:50Z

Codecov Report

✅ All modified and coverable lines are covered by tests.

📢 Thoughts on this report? Let us know!

cevheri

I checked the provider change, the export commit, the doc sections and the tests: the new tests fail without the fix and the docs match the code. One write path is still open.

Make an inline grid edit of a datetime or smalldatetime cell write the same date under every DATEFORMAT.
Cause: the edit box opens with the cell text (ResultsGrid.tsx:820), and use-inline-editing.ts binds it as an nvarchar @pN parameter, so it goes through the same DATEFORMAT conversion your §5.5 measurement shows. Before this PR the box held the ...Z form, which replays the same under every language; now it holds 2026-10-04 12:34:56.123, so a user on a british login who changes only the time gets April 10th stored.
Change: in use-inline-editing.ts, when the edited column's declared type (from result.columnTypes, read the way keyColumnKind reads it) is datetime or smalldatetime and the new value matches the provider's text form, send it with the space as a T. Export MSSQL_DATETIME_TEXT from typed-literals.ts and reuse it, so the export and the edit share one definition.
Done when: a test in tests/hooks/ shows an edit of a datetime and a smalldatetime cell binding 2026-10-04T12:34:56.123 while a datetime2 cell's text is bound unchanged, and docs/providers/mssql.md §5.3 or §5.5 mentions the edit path next to the export.

The grid opens its editor on the text the provider now reads these two types as, and the hook bound an edit of it as an nvarchar parameter, which SQL Server reads into them through the session's DATEFORMAT. Measured on SQL Server 2022 CU27 with the hook's own UPDATE under SET LANGUAGE british: 2026-10-04 12:34:57.123 was stored as April 10th and 2026-10-25 23:59:58.997 was refused as out of range, while the same text with a T was stored as written. The provider's own sessions read mdy today: tedious opens each one with set dateformat mdy and set language us_english, whatever the login's default language, and a SET LANGUAGE a user runs ends with its own sp_executesql call. So this keeps the edit from depending on that driver default rather than fixing a date stored wrong today. An edit of a datetime or smalldatetime column that is still in that form is now bound with its space as a T, matched by the MSSQL_DATETIME_TEXT the export uses, which typed-literals.ts now exports. The declared type is read the way keyColumnKind reads it, through two helpers split out of it. A datetime2 edit, and text in any other shape, is bound as typed. Refs libredb#1452

Aditya-XR · 2026-10-12T02:05:12Z

Thanks for the review. Done in 6822b4c, added on top of the two commits you reviewed (no rebase, so the diff is just this change):

MSSQL_DATETIME_TEXT is now exported from typed-literals.ts and use-inline-editing.ts uses it. If the edited column is declared datetime or smalldatetime and the new value still matches that text, the space is sent as a T. The type is read the same way keyColumnKind reads it: I split its unwrap and first-word steps into two small helpers so both use the same code. A datetime2 edit, NULL, and anything typed in another shape are sent unchanged, and only the mssql dialect is affected.
In tests/hooks/use-inline-editing.test.ts, one UPDATE edits a datetime and a smalldatetime cell, which bind 2026-10-04T12:34:56.123, and a datetime2 cell, which binds 2026-10-04 12:34:56.123 unchanged. There are also cases that have to stay as typed: a bare date, text that already has the T, four fraction digits, 04/10/2026 ..., datetimeoffset/date/nvarchar columns, no declared type, a MySQL datetime, and NULL. The main test fails on 3c48d1a, and I tried 9 mutations of the new code; the tests caught every one.
Docs: §5.5 has a paragraph on the edit path next to the export, and the §5.3 sentence now mentions both.

One thing I found while testing this live on SQL Server 2022 CU27 that you should know: through LibreDB's own connection the wrong date doesn't happen today. tedious starts every connection with set dateformat mdy and set language us_english, which overrides the login's default language. A login created with DEFAULT_LANGUAGE = british still shows us_english/mdy through the provider. With tedious's language and dateFormat options set to null, the same login gets British/dmy. A SET LANGUAGE the user runs doesn't stick either, because query() sends each statement through sp_executesql and the setting ends with that call, inside a transaction too. I ran the real hook against the server with 11 kinds of edit, on logins whose default language is british or Deutsch, after SET LANGUAGE/SET DATEFORMAT inside a transaction, and on the pool. All 110 edits were stored correctly on 3c48d1a, and the same with this commit.

The mechanism you described is real, though. The hook's own UPDATE ... SET [dt] = @p1, with SET LANGUAGE british in the same call, stored 2026-10-04 12:34:57.123 as April 10th and refused 2026-10-25 23:59:58.997. With the T, both were stored as written. So this protects the edit if the session ever stops being mdy (for example if the provider starts honouring the login's language); it isn't fixing a date that's stored wrong today. The docs and the code comment say it that way. I kept it as you asked, since it's small and keeps the edit and the export on one definition.

Full suite and coverage pass on Linux, and so do format, lint, typecheck and knip.

cevheri

Thanks @Aditya-XR. Checked 6822b4c: the new hook tests fail without the change and pass with it, the merge with main is clean, and tedious 20.3.0 does open with set dateformat mdy, so the docs say it right. Approving.

Aditya-XR added 2 commits October 10, 2026 05:13

Copilot AI balanced review requested due to automatic review settings October 10, 2026 05:28

Copilot AI reviewed Oct 10, 2026

cevheri requested changes Oct 11, 2026

View reviewed changes

cevheri assigned Aditya-XR Oct 12, 2026

cevheri added mssql database-provider labels Oct 12, 2026

cevheri approved these changes Oct 12, 2026

View reviewed changes

cevheri merged commit 2e7748a into libredb:main Oct 12, 2026
24 checks passed

This was referenced Oct 16, 2026

Oracle inline row editing binds a DATE or TIMESTAMP cell as a plain string, and Oracle refuses the UPDATE #1807

Closed

fix(editor): bind an Oracle DATE and TIMESTAMP edit through TO_DATE, TO_TIMESTAMP, or FROM_TZ #1818

Merged

Navigation Menu

Repository navigation

fix(mssql): read DATETIME and SMALLDATETIME as the engine's own text, and export them with a T - #1639

fix(mssql): read DATETIME and SMALLDATETIME as the engine's own text, and export them with a T#1639
cevheri merged 3 commits into
libredb:mainfrom
Aditya-XR:fix/1452-mssql-datetime-zoneless

Aditya-XR commented Oct 10, 2026

Uh oh!

Copilot AI left a comment

Uh oh!

codecov Bot commented Oct 10, 2026

Uh oh!

cevheri left a comment

Uh oh!

Aditya-XR commented Oct 12, 2026

Uh oh!

cevheri left a comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

Aditya-XR commented Oct 10, 2026

Description

Live measurement

Type of Change

Related Issue

Changes Made

Testing

Test Environment

Checklist

Additional Notes

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Uh oh!

codecov Bot commented Oct 10, 2026

Codecov Report

Uh oh!

cevheri left a comment

Choose a reason for hiding this comment

Uh oh!

Aditya-XR commented Oct 12, 2026

Uh oh!

cevheri left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants