"DATE" data type is deployed as "SMALLDATETIME" in SQL 2008

Description

I have a changeset below to create a table called "BATCH" with 2 columns below :

<changeSet author="NK" id="1">
<createTable schemaName="dbo" tableName="BATCH">
<column autoIncrement="true" name="sid" type="BIGINT">
<constraints nullable="false" primaryKey="true" primaryKeyName="PK_Batch"/>
</column>
<column name="default_date" type="DATE"/>
</createTable>
</changeSet>

When deployed against SQL Server 2008, the DEFAULT_DATE column is created as "SMALLDATETIME" where
what we really want is "DATE" data type.

Environment

Windows SQL Server 2008 R2

Activity

Show:
Martin Grotzke
August 23, 2013, 8:02 AM

We're also running into this issue with SQL Server 2008. What's the suggested workaround for this?
Can we do s.th. to help implementing the fix, any guidance? I'd change the DateType so that toDatabaseDataType does not return SMALLDATETIME for mssql, but maybe this is too naive (what about older versions of SQL Server)?

Michael Oberwasserlechner
August 27, 2013, 2:01 PM

Hi Martin,

If your using Liquibase 2.0.x, you can write your own MSSQL2008TypeConverter.

I was able to solve a DATETIME2 problem using such a converter.

see http://forum.liquibase.org/topic/date-and-time-types-for-mssql for the code (search for "public class MSSQLDateTypeConverter".

Cheers,
Michael

Martin Grotzke
August 28, 2013, 10:30 AM

Hi Michael,

thanx for your suggestion! We're using liquibase 3 and there's no MSSQLTypeConverter or TypeConverter anymore AFAICS.
Because I don't want to rely on the "modifySql/replace" solution (might be forgotten, it cannot be specified globally), I created a custom DateType that overrides toDatabaseDataType:

CustomDateType.java

It must be in package liquibase.ext (or any other package scanned by liquibase) to be picked up by liquibase, additionally the @DateTypeInfo.priority is higher than the default so that it's used in favour of DateType.

I'll add this info also to the "Date and Time types for MSSQL" forum thread.

Michael Oberwasserlechner
August 29, 2013, 9:51 PM

Hi Martin,

as I want to upgrade to 3.0.x soon, pointing out that DateType must be used instead of TypeConverter is a valueable input to me.

So thank.

Nathan Voxland
September 24, 2013, 1:16 PM

Fixed issue so that both sqlserver and sybase return "date" for date datatypes.

If you are wanting to use datetime2 use a extension as described or a changelog parameter or modifysql if you cannot just define the type as "datetime2" in your changelog.

Reporter

Nick Kladwang

Fix versions

Affects versions

Priority

Trivial
Configure