Oracle Date Timestamp

[shrey3a]

Hi,

I need to insert the current date time to Oracle the target field defination is 'Date' and has the data with ti,e stamp for e.g. 6/23/2004 10:47:00 AM

I tried using the TimeStamp(Date()) it gives the date date but time is all set to 00:00:00 . I need to populate the timestamp also as per the requirment.

Thanks in advance.

Regards

[johm73]

Try this:

OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:")

[ray.wurlod]

Or even this:

Code: Select all

OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTHS:[2,2,2]") 

(though it really is easier with a 24-hour clock - change your date picture accordingly).

[talk2shaanc]

or
1. Read it as a character throughout all the stages(even in the oracle stage)
2. Write a user-defined query , in which you use TO_DATE function, with a format that matches your data format.

[chulett]

Why, when the stage will do the TO_DATE for you automatically? Doing it that way forces the use of Custom SQL (yuck) for any table that has a DATE field in it.

If you stick with a Timestamp datatype in the Target OCI, you can still work with it as a string all through your job (if you like), as long as when you are done it is in the proper format for the stage: YYYY-MM-DD HH24:MI:SS. Then the generated SQL will do the TO_DATE conversion on that field and you'll be good to go.

[talk2shaanc]

yes you are right the generated sql query will also have TO_DATE format, but the format used in TO_DATE would be some default format, and it may not be same as your input data in that case you need to have User-defined query.

Again creating User-Defined query may intoduce some human error. But if we work smartly we can avoid it.

With working smartly i mean; first copy the generated sql query and then change the option to user-defined query and copy the query in the empty space and finally modify the format in the date function as per your input data.

[chulett]

yes ur right the generated sql query will also have TO_DATE format, but the format used in TO_DATE would be some default format, and it may not be same as your input data in that case you need to have User-defined query.

Need? Again, not really. You can either go the custom sql route (which I try to avoid if at all possible) to match your input date format, or you make sure your input date - regardless of its initial format - is always in the 'proper' format for the OCI stage when it hits it. One thing I bring with me in my Bag O' Tricks are some custom routines to handle taking dates / times in pretty much any format and getting them into an 'Oracle Timestamp' format. Everyone uses them, we have a consistant methodology for handling them and we don't have any issues with them.

[arunverma]

Use TO_CHAR function in user define sql while extracting data from source system .
Ex :- TO_CHAR(A.LAST_MODIFIED, 'YYYY-MM-DD HH24:MI:SS')
and make column data type Timestamp and while loading data into Oracle with using Oracle plug-in , use column data type as char(30) , it will load automatically in table as timestamp .

Regards

Arun