Hi Scott,

I'm getting an error everytime we try to sort by date.  Our custom field is set to 'one line text' with a validation of 'date'.  I believe this is correct...

This is the error (on user sort or default sort settings):

Error: Property Agent is currently unavailable. DotNetNuke.Services.Exceptions.ModuleLoadException: Arithmetic overflow error converting expression to data type datetime. The statement has been terminated. ---> System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at Ventrian.PropertyAgent.SqlDataProvider.ListProperty(Int32 moduleID, Int32 propertyTypeID, Int32 status, Int32 authorID, Int32 brokerID, Boolean isActive, Boolean isPending, Boolean isExpired, Boolean showFeaturedOnly, Int32 sortBy, Int32 sortByID, Int32 sortByIDType, Int32 sortOrder, String customFieldIDs, String searchValues, Int32 pageNumber, Int32 pageSize, Boolean bubbleFeatured, Boolean searchSubTypes, Int32 propertyIDForNextPrev, Double latitude, Double longitude, DateTime startDate) at Ventrian.PropertyAgent.PropertyController.List(Int32 moduleID, Int32 propertyTypeID, SearchStatusType status, Int32 authorID, Int32 brokerID, Boolean showFeaturedOnly, SortByType sortBy, Int32 sortByID, SortDirectionType sortOrder, String customFieldIDs, String searchValues, Int32 pageNumber, Int32 pageSize, Int32& totalRecords, Boolean bubbleFeatured, Boolean searchSubTypes, Int32 propertyIDForNextPrev, Int32& typePosition, Double latitude, Double longitude, DateTime startDate) at Ventrian.PropertyAgent.PropertyController.List(Int32 moduleID, Int32 propertyTypeID, SearchStatusType status, Int32 authorID, Int32 brokerID, Boolean showFeaturedOnly, SortByType sortBy, Int32 sortByID, SortDirectionType sortOrder, String customFieldIDs, String searchValues, Int32 pageNumber, Int32 pageSize, Int32& totalRecords, Boolean bubbleFeatured, Boolean searchSubTypes, Int32 propertyIDForNextPrev, Int32& typePosition) at Ventrian.PropertyAgent.ViewType.BindProperty() at Ventrian.PropertyAgent.ViewType.Page_Initialization(Object sender, EventArgs e) --- End of inner exception stack trace ---

Hi Scott,

That's what I thought.... so, I completely removed all the properties and re-entered them all using the date calendar - just in case i missed any last time - however it is still not working.

Is there anything else?  All the other sorts work fine, just not the date. 

Yes it has always been validated against date.  I also just upgraded as I saw a sort fix in the latest release but it's still not working.

Any help much appreciated.

Regards,

Anna

Hi Scott,

We are still getting this error - even on the latest version.

I dont suppose you've had any thoughts on what may be cuasing this?

A critical error has occurred.
Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.

Thanks,

Anna

Hi Scott & Anna

I am encountering the same problem when sorting on date. The localisation for the portal is NZ which uses the same date format as UK eg, dd/mm/yyyy. I have found the problem only occures when there is a date in the list that cannot be read as US date format mm/dd/yyyy.

EG. When I add a record with a date for 20th August it is saved in db as 20/08/2009. When I sort on this field, there is an error. If I change the date to 12th August 12/08/2009, there is no error as I suspect the sorting reads it as 8th December.

Hope this helps you find a fix for this Scott.

Cheers

Andy