problem with datetime custom field

Help
stmod
2011-05-17
2012-12-02
  • stmod

    stmod - 2011-05-17

    Hello,
             I am trying to add a customfield of tpe datetime (the classic deadline field).

    I am able to set up the field, but I am not able to use it. Whenever I try to fill it I get this error

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

    I get the same error using task/activities features.

    The field should be in the form yyyy-mm-dd

    I tried to use different format date (e.g. yyyymmdd or yyyy/mm/dd etc.) and even with the time…. I tried to copy/past field regarding date (i.e last modified date) but I alternatively get or the stated error or a message saying the date format is not the right one.

    I suspect this is a problem with sql server (I tried with both 2005 and 2008 version) and its configuration about date

    Here is the output of SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') in Sql server 2008

    10.50.1600.1   RTM    Express Edition with Advanced Services

    and then

    sp_configure 'default language'

    that produces

    default language 0 9999 0 0

    and this means

    0 mdy 7 0 us_english English

    someone can help me?

    Thanks

    stmod

     
  • Corey Trager

    Corey Trager - 2011-05-18

    If you have my logging turned on in Web.config, you can see the full text of the SQL statements in the log.  Can you post the insert text that doesn't work?

     
  • stmod

    stmod - 2011-05-18

    Hi,
        I had a look into the log and at the bottom I paste the generated query and the corresponding stack trace.

    I further investigated the problem and it should be the dot in the time of date format

    '20110525 00.00.00' should be '20110525 00:00:00'  with ":" insted of "."

    In this way i can insert (via sql manager) the bugs in the table.

    But, anyway, even if i do a copy paste of a right string on the input field of bugtracker form i get the error
    ""mydeadline" not in a valid date format.
      Bug was not created. "

    stmod

    sql:
    insert into bugs
    (bg_short_desc,
    bg_tags,
    bg_reported_user,
    bg_last_updated_user,
    bg_reported_date,
    bg_last_updated_date,
    bg_project,
    bg_org,
    bg_category,
    bg_priority,
    bg_status,
    bg_assigned_to_user,
    bg_user_defined_attribute,
    bg_project_custom_dropdown_value1,
    bg_project_custom_dropdown_value2,
    bg_project_custom_dropdown_value3
    ,,)
    values (N'test', N'', 1,  1, getdate(), getdate(),
    0, 1,
    0, 0, 1, 0, 0,
    N'',N'',N'' ,N'','20110525 00.00.00')

    stack trace:

    MSG: La conversione del tipo di dati da char a datetime ha generato un valore di tipo datetime non compreso nell'intervallo dei valori consentiti.
    L'istruzione è stata interrotta.
    URL: ---------------------
    EXCEPTION: System.Data.SqlClient.SqlException (0x80131904): La conversione del tipo di dati da char a datetime ha generato un valore di tipo datetime non compreso nell'intervallo dei valori consentiti.
    L'istruzione è stata interrotta.
       in System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       in System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       in System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       in System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       in System.Data.SqlClient.SqlDataReader.get_MetaData()
       in System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       in System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       in System.Data.SqlClient.SqlCommand.ExecuteScalar()
       in btnet.DbUtil.execute_scalar(String sql) in h:\btnet_3_5\www\App_Code\dbutil.cs:riga 27
       in btnet.Bug.insert_bug(String short_desc, Security security, String tags, Int32 projectid, Int32 orgid, Int32 categoryid, Int32 priorityid, Int32 statusid, Int32 assigned_to_userid, Int32 udfid, String project_custom_dropdown_value1, String project_custom_dropdown_value2, String project_custom_dropdown_value3, String comment_formated, String comment_search, String from, String cc, String content_type, Boolean internal_only, SortedDictionary`2 hash_custom_cols, Boolean send_notifications) in h:\btnet_3_5\www\App_Code\bug.cs:riga 940
       in ASP.edit_bug_aspx.on_update(Object sender, EventArgs e) in h:\btnet_3_5\www\edit_bug.aspx:riga 2158
       in System.Web.UI.HtmlControls.HtmlInputButton.OnServerClick(EventArgs e)
       in System.Web.UI.HtmlControls.HtmlInputButton.RaisePostBackEvent(String eventArgument)
       in System.Web.UI.HtmlControls.HtmlInputButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       in System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       in System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       in System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

     
  • stmod

    stmod - 2011-05-18

    I forgot this….

    Riga 25:                 object returnValue;
    Riga 26:                 SqlCommand cmd = new SqlCommand(sql, conn);
    Riga 27:                 returnValue = cmd.ExecuteScalar();
    Riga 28:                 conn.Close(); // redundant, but just to be clear
    Riga 29:                 return returnValue;

    the error is in row 27

     
  • stmod

    stmod - 2011-05-18

    and (sorry but i am in a hurry) the file is
    \btnet_3_5\www\App_Code\dbutil.cs   

     
  • Corey Trager

    Corey Trager - 2011-05-18

    Try adding or uncommenting this setting in your Web.config.
    <add key="SQLServerDateFormat" value="yyyyMMdd HH\:mm\:ss"/>

     
  • stmod

    stmod - 2011-05-18

    Thank you so much….

    It works fine…. maybe i should have had a better look on the web.config, but it is really a huge file….

    best regards

    stmod

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks