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
Oracle Date Timestamp
Moderators: chulett, rschirm, roy
Re: Oracle Date Timestamp
Try this:
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:")
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:")
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Or even this:
(though it really is easier with a 24-hour clock - change your date picture accordingly).
Code: Select all
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTHS:[2,2,2]")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
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.
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.
Shantanu Choudhary
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.talk2shaanc wrote: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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
Arun Verma