Setting for default column type

Question

Is there a setting in SQLServer 2008 and newer for default data type of date-time columns, i.e. datetime or datetime2? Actually more like something that would tell SQLServer to use datetime2 instead of datetime when creating a table/view even if the latter is requested explicitly.

---

UPDATE: Thank you everyone. With this issue I am actually on the pitching side, that is creating tables from Java application that are supposed to have datetime columns, but end up having datetime2. Thought it might be due to DBMS configuration, but, probably, it's due to intricacies of one of our frameworks. Oh well, back to digging code :)

P.S. Special thank you goes to @srutzky for detailed response with multiple options, but accepted @John M's answer as being the most spot on.

Answer 1

No, there isn't a way to automatically change a datetime field to a datetime2 field if the former is specified in a table creation.

To do it manually:

ALTER TABLE [table] MODIFY COLUMN [column] DATETIME2;

If the issue pertains to end users, education is your best bet to make the creation of tables more uniform and compliant with using datetime2.

Answer 2

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type.

Now, doing what amounts to a "default" (especially inline with your request to use DATETIME2 even if someone tries to use another type) is a bit complicated and shown below. However, it is much simpler to simply "disallow" use of particular datatypes. You could create a DDL trigger to trap CREATE TABLE and ALTER TABLE statements and if someone is trying to use DATETIME, simply issue the following:

ROLLBACK TRAN;
RAISERROR('How many times have I said: DON''T use DATETIME! Try again with DATETIME2.', 16, 1);
RETURN;`

This does not require complex text parsing or any additional logic. Nor will it make you many friends.

And I am not saying that one should do the following, but it is possible to use a DDL Trigger to trap CREATE TABLE statements (which is really after the event) and then scan the DDL submitted and if anything undesired was used, issue the appropriate ALTER TABLE statement. Just keep in mind:

• the CREATE TABLE statement might include constraints and indexes which might require being dropped in order to issue the ALTER TABLE, and then obviously re-added afterwards.

• this requires better text parsing than is usually possible with straight T-SQL as there is an incredible amount of flexibility in how you can write a query that is valid syntax. So you will need to make use of SQLCLR in order to get Regular Expression functionality (i.e. Match and Replace). I am not saying that this part is an issue, just more of an FYI.

So, this isn't exactly "simple". But it is at least possible and hence why I am mentioning it here because even if this specific scenario isn't advisable, the concept is at least transferable to other scenarios.