Results 1 to 11 of 11

Thread: Timestamp

  1. #1
    Join Date
    Sep 2008
    Posts
    16

    Unanswered: Timestamp

    Hello,

    I am trying to input timestamp as 1900-01-01 00:00:00 into oracle 10g.

    This works fine in SQl 2005 and IBM DB2 however, I cannot get this inserted into oracle. Anyone know any way on how to do this?

    Thanks,
    Dbansal

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    try

    Code:
    insert into table (SOME_DATE) values (TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'));
    You have to specify the elements of the date to Oracle. It will not make any assumptions, unless you tweak the NLS_DATE_FORMAT.

    --=cf

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dbansal
    I am trying to input timestamp as 1900-01-01 00:00:00 into oracle 10g.
    This sounds like a strange design. Why do you want to store such a date?
    If you want to mark the absence of the value use a NULL value.

  4. #4
    Join Date
    Feb 2005
    Posts
    57
    Show us the SQL you have used to try to insert the timestamp. Also what is the TYPE of the column you are inserting the timestamp into?

  5. #5
    Join Date
    Sep 2008
    Posts
    16
    Hello,

    The format is strange because that is the format that IBM DB2 will only take, and I have to make this work across IBM DB2, SQL 05, and Oracle 10g.

    The date is not an issue, 1900-01-01 00:00:00 is just arbitrary. I just wanted to show the format.

    The datatype in Oracle is TIMESTAMP. Here is the SQL statement I am using:

    Code:
    insert into wdb_notifications (event,eventdatetime) values ('testtestest','1900-01-01 00:00:00');
    I get the following error:"ORA-01843: not a valid month"

  6. #6
    Join Date
    Sep 2008
    Posts
    16
    Cf,

    How can I modify the nls_date_format? I am calling this tweak a UDF (user defined format) will this UDF stick in the schema of the database? So, if I am inserting the format below it will assume the correct format?

    Thanks,
    Darshan


    Quote Originally Posted by chuck_forbes
    try

    Code:
    insert into table (SOME_DATE) values (TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'));
    You have to specify the elements of the date to Oracle. It will not make any assumptions, unless you tweak the NLS_DATE_FORMAT.

    --=cf

  7. #7
    Join Date
    Sep 2008
    Posts
    16
    In other words I want to be able to change NLS_date_format and have it stick like that forever.

    so all I would have to do is insert into wdb_notifications (event,eventdatetime) values ('testtestest','1900-01-01 00:00:00');

    and it would work.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dbansal
    How can I modify the nls_date_format?
    If you use the to_date() function with a format model, you don't need to worry about nls_date_format.

  9. #9
    Join Date
    Sep 2008
    Posts
    16
    Quote Originally Posted by shammat
    If you use the to_date() function with a format model, you don't need to worry about nls_date_format.
    yes however, I cannot use that function in SQL 05 and IBM db2, I need a command that will work across all databases.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbansal
    ... I need a command that will work across all databases.
    give up this idea

    if you think using a single format to insert date values is problematic, wait until you start writing actual queries for things like the current date, transactions placed yesterday, and so on

    you simply must give up the idea that you can do date handling the same in all database systems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dbansal
    yes however, I cannot use that function in SQL 05 and IBM db2, I need a command that will work across all databases.
    If you are using JDBC you could use the JDBC format that is DBMS-independent, e.g.:
    Code:
    insert into table (SOME_DATE) 
    values ( {ts '2001-05-11 11:38:05'} );
    This might work with ODBC as well.

    There is an ISO standard for date and time literals that is supported by Oracle, Postgres and MySQL (not Microsoft SQL Server).
    That format would be
    Code:
    insert into table (SOME_DATE) 
    values 
    (TIMESTAMP '2001-05-11 11:38:05');
    I don't know if DB2 supports that though. You would need to check the manual.

    Doesn't your programming language support some kind of prepared statements? Thus you would not need to specify the literals, but could use the objects available in your programming language. That would be the most portable way.

Posting Permissions

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