Two questions: what does the SQL Server JDBC driver do when you do:

1. setObject(1, someDate, java.sql.Types.DATE)

...?  Liquibase should probably follow suit.

Next question: Are you aware that you can use changelog parameters?  It's kind of a hack for this purpose, but:

1. <property name="dateType" value="date"/>
2. <property name="dateType" value="someSQLServerSpecificDataType" dbms="mssql"/>

Then refer to it like this:

1. <column name="dateColumn" value="${dateType}"/>

Hope that helps.

Best,
Laird

I don't think that the behavior of

Let me clarify. The best solution I can think of for my purposes is to change the mappings to the ones suggested at the top. If it is not acceptable to make that, potentially breaking, change, then something like my suggestion would be needed.

The issue seems to be still there.
Possible reason for the odd mapping choice is that DATE/TIME/DATETIME2 were introduced only in SQL Server 2008 (see MSDN data types article).
An unpleasant aspect of the issue is that Hibernate does not allow mapping from SMALLDATETIME (Liquibase DATE) to java.sql.Date (aka Temporal.DATE).
I think the best solution would be to keep the current mapping for older MSSQL versions, and implement the new mapping proposed by Wesley for newer MSSQL versions.
A simple plugin can be used to workaround the DATE/TIME problem (I don't need DATETIME2 support, but it can be easily added too):

1. package liquibase.ext;
   
   import liquibase.database.Database;
   import liquibase.database.structure.type.DateType;
   import liquibase.database.structure.type.TimeType;
   import liquibase.database.typeconversion.core.MSSQLTypeConverter;
   import liquibase.exception.DatabaseException;
   import liquibase.servicelocator.PrioritizedService;
   
   /**
    * Custom Liquibase TypeConverter for Microsoft SQL Server 2008 and higher which supports proper DATE and TIME datatypes.
    * @author Sergey Parhomenko
    */
   public class MSSQLDateTypeConverter extends MSSQLTypeConverter {
       private static final int MSSQL_2008_VERSION = 10;
   
       @Override
       public boolean supports(Database database) {
           try {
               return super.supports(database) && database.getDatabaseMajorVersion() >= MSSQL_2008_VERSION;
           } catch (DatabaseException e) {
               return false;
           }
       }
   
       @Override
       public int getPriority() {
           return PrioritizedService.PRIORITY_DATABASE + 1;
       }
   
       @Override
       public DateType getDateType() {
           return new DateType("DATE");
       }
   
       @Override
       public TimeType getTimeType() {
           return new TimeType("TIME");
       }
   }

I am working on a better mapping system for 2.1 and I'll try to get this handled more as expected. I'll update this thread when I have something committed for people to try.

Is there any news on this as I have just encountered this issue.

I am busy working on changes to the logic around this and other diff/comparison code. It is ending up fairly extensive to the point of probably being called liquibase 3.0. I keep hoping it will be ready to have an initial beta in a few weeks, but it hasn't' made it yet, unfortunately.

For those that need a date column in mssql and that

• do not want to write extensions
• can't wait for LiquiBase 3.0

a work around is to use modifySql as shown here:

1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
           http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
       <!--property name="dateType" value="date" dbms="mssql"/-->
       <changeSet author="DELETE_ME!" id="straight">
           <createTable tableName="straight">
               <column name="DATE_COL" type="DATE" /><!-- => smalldatetime -->
               <column name="TIME_COL" type="TIME" /><!-- => datetime -->
               <column name="DATETIME_COL" type="DATETIME" /><!-- => datetime -->
               <column name="DATETIME2_TO_SECONDS_COL" type="DATETIME2(0)" /><!-- => datetime2(0) -->
               <column name="DATETIME2_TO_1000SECONDS_COL" type="DATETIME2(3)" /><!-- => datetime2(3) -->
           </createTable>
       </changeSet>
       <changeSet author="DELETE_ME!" id="with_modifysql">
           <createTable tableName="with_modifysql">
               <column name="DATE_COL" type="MSSQL_DATE" /><!-- => date -->
               <column name="TIME_COL" type="MSSQL_TIME" /><!-- => time(7) -->
               <column name="DATETIME_COL" type="DATETIME" /><!-- => datetime -->
               <column name="DATETIME2_TO_SECONDS_COL" type="DATETIME2(0)" /><!-- => datetime2(0) -->
               <column name="DATETIME2_TO_1000SECONDS_COL" type="DATETIME2(3)" /><!-- => datetime2(3) -->
           </createTable>
           <modifySql dbms="mssql">
               <replace replace="MSSQL_DATE" with="date" />
               <replace replace="MSSQL_TIME" with="time" />
           </modifySql>
       </changeSet>
   </databaseChangeLog>