locked
Cannot see TIMESTAMP RRS feed

  • Question

  • Hi ,

    I have a date field and in the Oracle data base and it is shown as date with TIME STAMP, however when i load into sql server db and run the query i cannot see the time stamp. How can i see the complete date with the time stamp.

    Please need help.

    Thanks

    Friday, January 8, 2016 4:41 PM

Answers

  • Good day,

    You cannot see data that is not in database. My first guess is that you did not load the data correctly into the SQL Server. It could help if you post the query that you uses in order to load the data  (or any other explanation regarding the loading process).

    Usually when the data is not loaded there is some error messages, which you did not provide. For example very common error while loading datetime from oracle to SQL Server is "Conversion failed because the data value overflowed the specified type."

    First solution is simply converting the Oracle date to an ISO string. for example using TO_CHAR(YourColumnAtOracle, 'YYYYMMDD HH:MI:SS')

    Another option is to import everything to a staging table with all column data types as NVARCHAR or VARCHAR, and next to convert the text into datetime while moving it to the final table.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, January 8, 2016 5:00 PM

All replies

  • How do you load the data into DB, which field is used for the date and what is its type?

    What is the query you run?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, January 8, 2016 4:52 PM
  • Good day,

    You cannot see data that is not in database. My first guess is that you did not load the data correctly into the SQL Server. It could help if you post the query that you uses in order to load the data  (or any other explanation regarding the loading process).

    Usually when the data is not loaded there is some error messages, which you did not provide. For example very common error while loading datetime from oracle to SQL Server is "Conversion failed because the data value overflowed the specified type."

    First solution is simply converting the Oracle date to an ISO string. for example using TO_CHAR(YourColumnAtOracle, 'YYYYMMDD HH:MI:SS')

    Another option is to import everything to a staging table with all column data types as NVARCHAR or VARCHAR, and next to convert the text into datetime while moving it to the final table.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, January 8, 2016 5:00 PM
  • How do you load the data into DB, which field is used for the date and what is its type?

    What is the query you run?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sorry Naomi and Ronen,

    I'm rephrasing my question. My source is a Oracle db and my destination is a flat file. I'm using SSIS Package to load the data. In Oracle db i see a date field (datetime stamp), when i load that into flat file i do not see the TIMESTAMP, only the date is loaded. Its just a simple load of data from one table no transformations are involved.

    Thanks

    Friday, January 8, 2016 5:15 PM
  • Amy, they are asking you what the data type of the output column is.

    If the output column is DATE it is not going to show you the time as well. You need to ensure the output datatype is something compatible with the TIME portion.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Friday, January 8, 2016 5:57 PM
  • Amy, they are asking you what the data type of the output column is.

    If the output column is DATE it is not going to show you the time as well. You need to ensure the output datatype is something compatible with the TIME portion.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thanks Patrick the Output column is DATETIME and the destination is a .csv file
    Friday, January 8, 2016 8:00 PM
  • Sorry Naomi and Ronen,

    I'm rephrasing my question. My source is a Oracle db and my destination is a flat file. I'm using SSIS Package to load the data. In Oracle db i see a date field (datetime stamp), when i load that into flat file i do not see the TIMESTAMP, only the date is loaded. Its just a simple load of data from one table no transformations are involved.

    Thanks

    >> I'm rephrasing my question....

    I recommend to add response in the end and not in the middle of the thread. If you respond several message, then I recommend to put the response in the last message. In this way the thread will not look like it is break or that there is a message that you did not respond to :-)

    >> My source is a Oracle db and my destination is a flat file.

    According to your new description it look to me that there is no reason to use SQL Server. You should use Oracle tools to export the data as flat file.

    >> I'm using SSIS Package to load the data.

    Again I do not see the reason to use SSIS when SQL Server has nothing to do in the processes :-)

    >> In Oracle db i see a date field (datetime stamp), when i load that into flat file i do not see the TIMESTAMP, only the date is loaded.

    Since you are using SSIS in the process:

    1. Option 1: converting issue - read my previous response. The solution is the same and the issue is the same. You probably does not convert the data from the oracle before you use it.

    2. Option 2 (I guess this is your case): Oracle datetime is not like SQL Server Dtaetimne but like SQL Server datetime2. Your data might be outside the range of datetime type. Try to use datetime2 in the SQL Server.

    => Oracle date in datetime supported range is '1000-01-01' to '9999-12-31'
    => SQL Server date in datetime supported range is January 1, 1753, through December 31, 9999

    * In any case for any case, it is better to use datetime2 in SQL Server and not datetime :-)

    >> Its just a simple load of data from one table no transformations are involved.

    This is FAR from been simple load! this is like going from point 1 to point 3 through points 4,5,6,7,8,9 and back to point 2 in order to get to point 3, instead of going from point 1 directly through point 2 to point 3...

    The SQL Server (SSIS) has nothing to do in your process :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Friday, January 8, 2016 8:08 PM
    Friday, January 8, 2016 8:07 PM
  • Hi AmyBI,

    When data enters a data flow in a package, the source that extracts the data converts the data to an Integration Services data type, the guidance on mapping the data types used by certain databases to Integration Services data types in this link is for your reference.

    Sam Zha
    TechNet Community Support

    • Proposed as answer by Naomi N Tuesday, January 12, 2016 1:17 PM
    Tuesday, January 12, 2016 12:42 PM
  • thinkingeye,

    I see the message that you deleted... why did you deleted your own response :-( ?!?
    I loved your (deleted) response :-)

    As much as I understand the issue is close. If so, please close the thread (by marking the answer/s, and voting responses). If this is still an issue please clarify how we can help you more :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, January 12, 2016 6:45 PM