none
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


    Adeel
    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

    MM/dd/yy

    With any other date format like this

    dddd, MMMM dd, yyyy
    dd-MMM-yy
    MMM-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?


    Adeel
    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
    Adeel
    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
    Adeel
    Friday, June 4, 2010 4:45 AM
  • Take a look at this link for using paramaters as per Paul's reply

    http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/uldnen10/dn-lesson-buttons-dotnet-tutorial.html

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

    Friday, June 4, 2010 5:05 AM