nanoseconds not returned from Oracle

Posted on 2006-04-17
Last Modified: 2008-01-09
I have a Timestamp(6) column in Oracle called EVENT_DATE, it is the primary key of the table.  
Example data copied directly from TOAD = 4/17/2006 12:13:06.478911 PM

I'm trying to return this value to a dataset, then a datagrid, and then pass this value to another page
as the key to display detailed data for the selected row.  However the nanoseconds are not being returned to the dataset, thus the key is incomplete when I go to search for the detail row.  
Sample SQL: "SELECT * from jobtrk.EVENTS where EVENT_TYPE = 'ERROR' and EVENT_DATE >= to_date('4/17/2006','MM/DD/YYYY');"

This SQL returns four rows to the dataset then on the datagrid, which is what I expect.  Executed in TOAD
it brings back four rows and the EVENT_DATE column is displayed including nanoseconds.  But when I view
the column in the dataset it looks like:
#4/17/2006 12:13:06 PM# {Date}
    Date: #4/17/2006 12:13:06 PM#

Am I missing something from the SQL?  
Do I need to redefine something in the dataset to utilize the full timestamp?  
Question by:bozworthy
    LVL 96

    Expert Comment

    by:Bob Learned
    ds.Tables(0).Rows(0).Item(0).ToString("dd MMM yy HH:mm:ss:fffffff")


    LVL 1

    Author Comment


    returns ==> "4/17/2006 12:13:06 PM"

    ?ds.Tables(0).Rows(0).Item(0).ToString("dd MMM yy HH:mm:ss:fffffff")
    returns ==>  Conversion from string "dd MMM yy HH:mm:ss:fffffff" to type 'Integer' is not valid

    I also tried several variations of that format string and get the same error with some invalid
    cast exceptions depending on what I try.
    LVL 96

    Accepted Solution

    ?CType(ds.Tables(0).Rows(0).Item(0), DateTime).ToString("dd MMM yy HH:mm:ss:fffffff")

    LVL 1

    Author Comment

    That is excellent.  

    (For the record my Oracle field is storing six decimal places after the seconds. The solution posed above has an extra "f", which is adding a zero to the end of the timestamp and would cause the subsequent lookup to fail)  

    Thanks much.
    LVL 1

    Author Comment

    Actually now I need to post another question about how to get this value into the datagrid during the DataBind...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now