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 name="default_date" type="DATE"/>
When deployed against SQL Server 2008, the DEFAULT_DATE column is created as "SMALLDATETIME" where
what we really want is "DATE" data type.
Windows SQL Server 2008 R2
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)?
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".
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:
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.
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.
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.