SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Apr 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Syntax of date, time, or timestamp value not valid (was "need help here")

    greetings...

    i have a problrms with sql..can somebody help me?

    i try to insert timestamp into my table but can't.

    my code:

    insert into tstest(ts) values ('2005-04-22 15:46:28.130257')

    error:

    Syntax of date, time, or timestamp value not valid. Cause . . . . . : The string representation of a date, time, or timestamp value does not conform to the syntax for the specified or implied data type and format. *N is either the character string constant that is not valid or the column or host variable that contained the string. If the name is *N, then the value is an expression specified in the statement. If the string was found in a host variable, the host variable number is 1. Recovery . . . : Ensure that the date, time, or timestamp value conforms to the syntax for the data type it represents. Try the request again.

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you don't need to insert anything into a timestamp field, the database server will do this for you

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, if ts column is of TIMESTAMP datatype, it's not neccessary to insert milliseconds because TIMESTAMP values stored in 'YYYY-MM-DD HH:MM:SS' format. Check 11.1.2. Overview of Date and Time Types

    TIMESTAMP[(M)]

    A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037.
    A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. The first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you don't assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value.
    From MySQL 4.1 on, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. .............
    Thus your INSERT statement works fine on MySQL 4.1, it may cause an error on MySQL 4.0 or below. Try to remove milliseconds


Bookmarks

Posting Permissions

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