none
Error when approving task updates on an enterprise custom date field. RRS feed

  • Question

  • Project Server 2010

    I have a repeatable issue with accepting task updates on a custom field.  The field is a date field with no roll up or roll down, not required, no formulas, no nothing.  Other than being a type=date field, there are no other constraints set.

    When my users update the field in the "My Assignments" view, there are no problems, however when they remove the date by clicking in the cell and hitting the delete key, I get an error when I try to approve their update.  The ULS log says:

    PSI: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

    I can confirm that they are not clicking in the cell and hitting the space bar.  I thought perhaps they were trying to send a blank space through.  That's not the case.  As a matter of fact, since it's defined as a date field, it won't let you do that anyway.  So, surely there's a way they can remove the date from this field and send me the update.

    Thursday, December 19, 2013 10:55 PM

All replies


  • You check in custom field and look up field in server setting whether your custom field is required field? if is it then you have to fill date. You can remove this property then check.

    Also if you dont want any value in the field then i would suggest you to remove field from My Assignment View from Manage view under server setting.               

    As custom field of type date, it is asking your resource to fill a value between 1/1/1753 to 12/31/9999.

     


    kirtesh

    Friday, December 20, 2013 5:40 AM
  • After more investigation I found hotfix for your issue.

    it would solve your problem ask your Project server admin to install Service pack 2.

    http://support.microsoft.com/kb/2598375/en-us

    Hot fix is having solution for your issue its a old CU so I would recommend you to install project server service pack 2 which will have all these CU.


    kirtesh

    Friday, December 20, 2013 9:32 AM
  • Thank you kirtesh, but that hotfix has already been applied to our server.  The hotfix mentions a date issue with a custom date field, but it's not the same issue we're experiencing anyway.
    Friday, December 20, 2013 2:24 PM
  • Please install service pack 2 for project server 2010 it will resolve your issue.

    http://support.microsoft.com/kb/2598375/en-us this hot fix is having fix for your kind of issues . Service pack is containing this hot fix.


    kirtesh

    Friday, December 20, 2013 2:30 PM
  • Hi Kevin,

    I guess you already check this out, but ensure that this date field is not marked as required. Indeed the error message shows that it must contain a value, meaning that if you delete the value, it will return an error message.

    In your process, can't you have today's date as the default date? It will provide you a workaround.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Friday, December 20, 2013 2:34 PM
    Moderator
  • Our server is at SP2.  I should have mentioned that at the beginning.
    Friday, December 20, 2013 2:34 PM
  • Hi Guillaume,

    Thanks for your reply.  Yes, I checked to see if the field was required.  I mentioned it in my original post.  It is not.  I'm afraid a default date of today (or any other day) will not work for this field's intended purpose.  I considered making it a text field instead, but I really don't want to do that.  I like knowing that the date will come through formatted the same way from all users.  If I make it a text field, I'll get all kinds of different date formats.  That would make things difficult for my schedulers, but it would solve the issue.  It's a tradeoff I'm not prepared to make just yet. :)

    Kevin

    Friday, December 20, 2013 2:40 PM
  • Hi Kevin,

    I do understand your concern. I'll let more It guys try to find a solution for this issue.

    BTW, notice that you can set a mask for text custom field in order to validate a date format.

    Regards.


    Guillaume Rouyre - MBA, MCP, MCTS

    Friday, December 20, 2013 2:43 PM
    Moderator
  • Kevin, 

    I tried to understand the exception and found :

    There are validators in place to check if the date entered is MM/dd/yyyy format and the user has to type it in. Even if you enter today's date, it does not return any error but saves the date as 1/1/0001 in the database. If I leave it blank, which is an OK condition as that field is not required, then I get this exception.

    So this is an exception which is because of Sql server data type. When you create custom field of date type it create Custom field in data base which is of Date data type which is gving problem. when you are approving the assignment, you actually pushing the value in DB hence getting exception as sql is having this exception.

    resolution of this as per sql experts you have to put datetime.today means default date as today in the custom filed. 



    kirtesh

    Friday, December 20, 2013 2:59 PM
  • Thanks kirtesh.

    That's disappointing.  From a programming perspective, there's no reason PWA shouldn't be able to pass a NULL back to the table.  In my opinion, that's the expected behavior if you make a field "not required." 

    In any custom code I've written, I can pass a NULL back to my SQL table without issue.  I suspect I could write a PSI call on a specific event to mimic the behavior I want, but at some point the level of effort outweighs the benefit. :(

    I appreciate your help though!

    Kevin

    Friday, December 20, 2013 9:06 PM