Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2008
    Posts
    29

    Arrow Unanswered: Help abut TimeStamp data type

    Hi all,
    I created one table ex: ( CREATE TAB1 ( COL1 VARCHAR(10), COL2 TIMESTAMP))

    I want to insert ( date, time with mil sec) into the COL2. I used NOW(). But i am getting Date, time with seconds.
    But i want date, time with mil sec.

    Can any one help please.
    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For those that don't know (Suni - you are unusual - very few people use JET DDL) TIMESTAMP is the same as DateTime. I thought I would mention this as TIMESTAMP is totally different in SQL Server.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2008
    Posts
    29
    not understood what u said pootle. Any other data types which inserts date, time with mil secs. Because i had one table which did not had unique column. So how i can recognize individual column. for that i created one timestamp column. but at the same time more than 1 record is inserting. to avoid this problem i tried for inserting mil secs also. Ok can u tell me any alternative to recognize individual column. Like ROWNUM in oracle. Or any thing here like ROWID. I am new to Access. I am using Access 2003.

    Can u tell me please.
    Thanks in advance.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You want the values in the TIMESTAMP column to be unique?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2008
    Posts
    29
    yes pootle. I want the timestamp column should be unique.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you cannot gurantee a timestamp column will be unique in a multi user environment. there is an infinitessimal rsik that two users may attempt to write a record at the same time. Id strongly reccomend you use an autonumbner column in place, or youwill have to set a unique index and trap for errors every time you try to write the value. there is a risk that someone could alter the value of the columns

    http://mail.localplanet.co.uk/Blogs/...Timestamp.aspx

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, I suspect that you hope that if you insert 20 records in one query using NOW() for the datetime, and the TIMESTAMP uses milliseconds, then the 20 records will have different values. This is not the case - all 20 will be identical no matter what the precision of the column.

    You can use a combination of your idea and Mark's - a timestamp and autonumber composite primary key\ unique index. The autonumber works as a "uniquifier" for your date column. This is assuming your queries will be of the sort "get me all records between x datetime and y datetime". If not, then the autonumber on its own would probably suffice as the PK.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2008
    Posts
    29
    hi healdem, pootle, all,
    Ok i will try autonumber. But what i am doing actually. I created one table and inserting records from another table select query. At that time When the records from select query inserting to new table, is there any function or any thing to get autonumber into new table.
    Thanks

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Autonumber is a special sort of datatype. It is not a function. It will automatically increment for you. Try it and see.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2008
    Posts
    29
    That mean shell i do like this CREATE TABLE TAB1 ( COL1 AUTONUMBER, COL2 VARCHAR(10))

    Please.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by suni
    That mean shell i do like this CREATE TABLE TAB1 ( COL1 AUTONUMBER, COL2 VARCHAR(10))
    Quote Originally Posted by pootle
    Try it and see.
    .
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2008
    Posts
    29
    ya by executing this ( CREATE TABLE TAB1 ( COL1 AUTONUMBER, COL2 VARCHAR(10)) ), i am getting error as Syntax error in field definition.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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