Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    10

    Unhappy Unanswered: Hour problems with Access/MySQL

    Hi,

    I've been trying to update a DATETIME field which is supposed to store the Hour of an event, but when I try to write the value to the MySQL database, it doesn't store the right value.

    I started doing this:

    rsContinuidad.AddNew
    rsContinuidad!bytEspacioDia = bytContador
    rsContinuidad!datFecha = datFechaDia
    rsContinuidad!idEmpresa = rsProgramacion!idEmpresa
    rsContinuidad!datHoraInicio = rsProgramacion!datHoraInicio <- These are Hour fields
    rsContinuidad!idPrograma = rsProgramacion!idPrograma
    rsContinuidad.Update

    but MySQL stored just the day, not the hour. Then I tried this INSERT query:

    stSQL = "INSERT INTO tblContinuidadEncabezado (datFecha, idEmpresa, bytEspacioDia, datHoraInicio, idPrograma) " & _
    "VALUES ('" & Format(datFechaDia, "YYYY-MM-DD") & "'," & rsProgramacion!idEmpresa & "," & bytContador & _
    ",'1899-12-30 " & Format(rsProgramacion!datHoraInicio, "Hh:Nns") & "'," & rsProgramacion!idPrograma & ");"
    Application.CurrentProject.Connection.Execute stSQL

    The resulting query is this:
    INSERT INTO tblContinuidadEncabezado (datFecha, idEmpresa, bytEspacioDia, datHoraInicio, idPrograma) VALUES ('2005-12-01',3,0,'1899-12-30 05:55:00',98);

    with similar results, it stores the date in the Database, but not the hour.

    Any ideas?
    Last edited by nvargas; 12-13-05 at 13:52. Reason: Adding some more info

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    HI NVargas
    There's nothuing wrong with date time column
    your format function explicitly request year month day

    I'd suggest you look at the format fucntion and see the extra codes required for the time

    HTH

  3. #3
    Join Date
    Feb 2005
    Posts
    10
    I've been reading the MySQL documentation about this, and the syntax I'm using for the datetime field should be right, but it isn't storing the data in full. If I check the db using phpMyAdmin, this is what I see:

    Edit Delete 2005-12-01 3 0 1899-12-30 98

    Remember, the query was:

    INSERT INTO tblContinuidadEncabezado (datFecha, idEmpresa, bytEspacioDia, datHoraInicio, idPrograma) VALUES ('2005-12-01',3,0,'1899-12-30 05:55:00',98);

    The resulting value is in bold.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry was looking at your first date, assumed 1889 wouldn't have a time attached.

  5. #5
    Join Date
    Feb 2005
    Posts
    10
    Well, here is the answer: My field wasn't datetime but DATE. I fixed it and works just fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •