Problem with DataTime Format RRS feed

  • Question

  • I am using Advantage Data Provider as a backend. The problem is when I change the system date time format, it throws error in the database. How can I overcome this limitation? Sybase iAnywhere accepts only one date format that is "06/02/2010"

    Thanks in Advance

    Wednesday, June 2, 2010 1:32 PM

All replies

  • No code posted so I have to ask how you are defining the Date parameter in your query?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, June 2, 2010 3:53 PM
  • This is how it is


     string CommandText = "INSERT INTO tasks (RuleID,StartDate,EndDate,SourceUnit,TargetUnit,Frequency,ScheduleTime,TaskType,SubTasks,CreatedBy,DateCreated) VALUES (" 
         + objtaskBE.RuleSerial  + ",'" 
         + objtaskBE.StartDate  + "','" 
         + objtaskBE.EndDate   + "'," 
         + objtaskBE.SourceUnit  + "," 
         + objtaskBE.TargetUnit  + ",'" 
         + objtaskBE.Frequency  + "','" 
         + objtaskBE.ScheduleTime + "','"
         + objtaskBE.TaskType  + "',"
         + objtaskBE.SubTasks  + ",'"
         + LoginBE.loggedinUser.ToString()
         + "','" + System.DateTime.Now.ToString("MM/dd/yyyy")
         + "');SELECT LASTAUTOINC( STATEMENT ) FROM System.iota;";

    It only works fine with this date format


    With any other date format like this

    dddd, MMMM dd, yyyy

    Its throwing error because Sybase iAnywhere accepts only one date format that is MM/dd/yy -- "06/02/2010" . Even if I change the separater from '/' to any other thing, the database does'nt accept it. The problem is mainly with long months. So I think the problem is with the back-end.

    So how can I make it work well with any date format that the user can change in control panel in the database?

    Thursday, June 3, 2010 1:02 PM
  • What is the data type of the column in the database? Is it Text or Date?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, June 3, 2010 2:01 PM
  • It is date. It throws error stating "Invalid Date Time" whenever I change the system date format
    Friday, June 4, 2010 3:54 AM
  • Have you tried using Command Parameters? This usually eliminates the need to be concerned with date formats because internally they are stored numerically.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, June 4, 2010 4:40 AM
  • Not yet. Unfortunately, I have'nt discover a way to use Stored Procedures in Sybase iAnywhere database yet. Everything is through inline queries
    Friday, June 4, 2010 4:45 AM
  • Take a look at this link for using paramaters as per Paul's reply

    Can you try setting the parameter type to datetime or date?

    Friday, June 4, 2010 5:05 AM