add `datetime2` data type.

[ping-maxwell]

issue

MSSQL datetime only stores time to an accuracy of ~3.33 milliseconds. This means when you insert a JS date into a datetime column, SQL Server rounds the fractional seconds to the nearest 0, 3, or 7 ms increment.

Example:
JS: 2025-09-26T01:00:00.001Z
MSSQL stored: 2025-09-26T01:00:00.003Z

solution

In order to resolve this, we must create the datetime field as datetime2 - however as of now using db.schema.alterTable(...).addField("name", "datetime2", ...) won't work.

For more context, here's a post on stackoverflow talking about this specific issue regarding mssql:
https://stackoverflow.com/questions/7823966/milliseconds-in-my-datetime-changes-when-stored-in-sql-server

Additional info:
I require the .addField specifically as this is being built into a wrapper function used in the better-auth repo.

[koskimas]

You can use any datatype using raw sql:

.addField("name", sql`datetime2`)

In general, whenever something's not possible, use the sql template tag.

We should add datetime2 to the types too probably.

[ping-maxwell]

In general, whenever something's not possible, use the sql template tag.

Gotcha, thanks a lot!

We should add datetime2 to the types too probably.

Yes that would be awesome.

Thanks for the quick replies!

[igalklebanov]

Hey 👋

Reopened to track datetime2 addition.

[ping-maxwell]

Hey sure.

On my end using sql to set the field works well, however I've been running into issues when dealing with querying fields related to dates when using certain operators like >, >=, <.
The returned values are unexpected with sometimes returning more rows and other times less, and I presume it's related to how datetime2 isn't properly supported yet.

In our tests, normal datetime works fine.

I can give more information if you would like

[igalklebanov]

Current MSSQL dialect implementation translates Date parameters to DateTime. That's a miscalculation on my part, as DateTime2 is the recommended data type since 2008.

So beyond adding datetime2 officially in data type union type and casting runtime validations, we also need to change the default dialect behavior to use DateTime2 in translation of parameters. That's a breaking change. At best, it'll slow down queries for DateTime columns - engine has to do conversion, so existing indexes won't be helpful. At worst, it'll affect query results.

Generally, the limitations of tedious (the underlying driver) - having to tell it which data type is a parameter, might require providing consumers with parameter wrappers that dictate the translation. e.g. where('createdAt', '>', datetime(createdAt)).

Rolling these changes will require a minor release - so it'll have to wait a bit.

As a quick workaround, when passing TYPES to the dialect, you can swap DateTime with DateTime2:

      tedious: {
        ...Tedious,
        connectionFactory: () => new Tedious.Connection({...}),
        TYPES: {
          ...Tedious.TYPES,
          DateTime: Tedious.TYPES.DateTime2,
        },
      },

Lemme know if this is helpful.