The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value

Question

I am using sql server 2008, NHIbernate and running this piece of code

select time0_.[Date] as Date2_8_, time0_.Hours as Hours8_, time0_.AuthorizedDate as Authoriz7_8_, time0_.AuthorizedBy as Authoriz9_8_ from [Time] time0_ where (UserID='4' )AND(Date='5/26/2011 12:00:00 AM' ) is giving me the error message:

{"The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value."}

NHibernate for Date and AuthorizedDate:

<property name="Date" type="DateTime">
        <column name="`Date`" length="4" sql-type="smalldatetime" not-null="true"/>
    </property>
<property name="authorizedDate" type="DateTime" access="field">
        <column name="AuthorizedDate" length="4" sql-type="smalldatetime" not-null="false"/>
    </property>

But if I take the piece of sql and run it from sql management studio, it will run without issue.

The DateTime format for the sql server is English (US) and the datetime format for my machine is English(US) as well.

I have tried Error: The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value and conversion of a varchar data type to a datetime data type resulted in an out-of-range value. But wasn`t able to solve my problem.

Any ideas what may be causing this issue?

danyolgiax danyolgiax · Accepted Answer · 2011-06-14T07:24:41

Why have you mapped it in this way?

You can try map it simply:

<property name="Date" not-null="true" />

try also to remove length="4"

update

Here some doc: Date/Time Support in NHibernate

DbType.DateTime maps to the datetime (or smalldatetime) SQL type

So I think don't need to specify it in mapping file.

However I think the base problem here is the date format. If I try locally in SSMS with '5/26/2011 12:00:00 AM' my query returns correctly but if I try '26/5/2011 12:00:00 AM' I've got the error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Have you tried setting localization in your application?

The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value

1 Answers