Search code examples
c#oracle-databasefluent-nhibernatefluent-nhibernate-mapping

How do I map an Oracle timestamp to a DateTime in Fluent NHibernate?


I have a column in Oracle that is a TIMESTAMP(6) WITH TIME ZONE. I am getting an exception when attempting to load records from the table: System.ArgumentOutOfRangeException : Year, Month, and Day parameters describe an un-representable DateTime.

Here is what I have tried for the mappings:

Map(x => x.ExpirationDate, "EXPIRE_DATE").CustomType("timestamp").CustomSqlType("TIMESTAMP(6) WITH TIME ZONE").Nullable();

Map(x => x.ExpirationDate, "EXPIRE_DATE").CustomSqlType("TIMESTAMP(6) WITH TIME ZONE").Nullable();

Map(x => x.ExpirationDate, "EXPIRE_DATE").CustomSqlType("timestamp").Nullable();

Map(x => x.ExpirationDate, "EXPIRE_DATE").CustomType("timestamp").CustomSqlType("timestamp").Nullable();

Solution

  • I ended up using an Oracle function inside the SQL in order to get a date time out. See the EXPIRE_DATE below:

    string sql = @"SELECT CONTAINER_ID,
    PLANT_ID,
    MATERIAL_ID,
    QTY_IN,
    QTY_OUT,
    UNIT_OF_MEASURE,
    TO_CHAR (EXPIRE_DATE, 'YYYY-MON-DD HH24:MI:SS') AS MY_EXPIRE_DATE,
    LOT_ID,
    CONTAINER_STATUS,
    CONTENTS_TYPE
    FROM POMSNET.MM_CONTAINER_ST
    WHERE CONTENTS_TYPE = 'Raw Material'
    AND QTY_IN - QTY_OUT > 0";