Date and Time types for MSSQL

General Discussion
1

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

    Hope that helps.

    Best,
    Laird

    2

    It seems to me that the choice of data types for date and time types in SQL server is incorrect, or possibly just out-dated. The standard at my company is to use the DATETIME2 data type in preference over the DATETIME data type on SQL Server, and the DATE type where a time value is not required. The current mapping choices for SQL Server in Liquibase seem strange:


    Wesley

    3

    I don’t think that the behavior of

    1. Wesley
    4

    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.

    5

    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”);
          }
      }

    6

    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.


    Nathan

    7

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

    8

    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.


    Nathan

    9

    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    

    10

    TypeConverter anymore.

    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 so that DATE is not converted to SMALLDATETIME for SQL Server: 


    1. package liquibase.ext;  import liquibase.database.Database; import liquibase.datatype.DataTypeInfo; import liquibase.datatype.DatabaseDataType; import liquibase.datatype.LiquibaseDataType; import liquibase.datatype.core.DateType;  /** * A custom {@link DateType} that does not convert DATE to SMALLDATETIME for SQL Server * as the default {@link DateType} does it. Must be in the classpath of liquibase to * be picked up and to be used instead of the default {@link DateType}. * 
       * Note: * 
      

    2. It's in the package liquibase.ext because this is scanned by liquibase * by default (according to {@link liquibase.servicelocator.ServiceLocator#setResourceAccessor}). * 
      3.  * 
    3. The priority is higher than default so that it's used in favour of {@link DateType}.
      4.  * 
       * * @author Martin Grotzke */ @DataTypeInfo(name = "date", aliases = { "java.sql.Types.DATE", "java.sql.Date", "smalldatetime" }, minParameters = 0, maxParameters = 0, priority = LiquibaseDataType.PRIORITY_DEFAULT + 1) public class CustomDateType extends DateType {  @Override public DatabaseDataType toDatabaseDataType(final Database database) { // Don't use SMALLDATETIME for MSSQLDatabase return new DatabaseDataType(getName()); }  }


    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.

    11

    CORE-2217 which is due for Liquibase 3.4.0 will vastly improve support for SQL Server data types including these date/time types. I’ve added a rigorous test suite that encompasses all built-in types. Have a look at the merged pull request #398 .