datetime2 vs. datetime, in Sql Server 2008+

[Jeff Dege]

Sql Server's "datetime" type has severely limited range and precision. MS introduced the "datetime2" type with Sql Server 2008, to address these issues, and that has been the recommended datatype in new applications, since then.

We've been using FluentMigrator to create tables against a Sql Server 2012 database (using the SqlServer2012ProcessorFactory), and we've been seeing AsDateTime() columns being created with "datetime" types, instead of "datetime2".

Since our system requires millisecond precision, and the "datetime" type doesn't provide millisecond precision, that's a problem for us.

So now we're faced with two issues:

1. How to create a migration that creates a column with type "datetime2"?
   
2. How to create a migration that converts a column with type "datetime" to type "datetime2"?

and a question:

1. Should FluentMigrator create columns with type "datetime2" by default, when running against SqlServer 2008+?

[Jeff Dege]

Turns out, the issues are simple:

AsCustom("datetime2")

The question, though, remains.

[Dru Sellers]

Agreed, I do the same with 'jsonb' and 'json' for Postgres. The fix I bet is even easy, so its all about what is expected.

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fluentmigrator-goog...@googlegroups.com.
To post to this group, send email to fluentmigrato...@googlegroups.com.
Visit this group at http://groups.google.com/group/fluentmigrator-google-group.
For more options, visit https://groups.google.com/d/optout.

[Tom Marien]

Hi

For the time being you will have to use like already mentioned the ascustom. But in the next release you will have it trough the api. As far as setting it as default, i don't agree, by default datetime in ms sql server is accurate up to 4 ms. Which is more then enough if you just need datetime.

Verstuurd vanaf mijn iPad

[Jeff Dege]

I was suggesting that you use datetime2 as the default, in Sql Server, because Microsoft recommends that you use datetime2, rather than datetime:

From their documentation for datetime:

https://msdn.microsoft.com/en-us/library/ms187819.aspx

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time,datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.