Date conversion from time stamp to yyyy/mm/dd
Moderators: chulett, rschirm, roy
Date conversion from time stamp to yyyy/mm/dd
I know this topic has been covered many times, but could not get the exact answer
I want to convert date from 2005-04-15 00:00:00 to 2005-04-15 or 2005/04/15.
My destination table in oracle is Date. In datastage it is a Timestamp
Can anybody help?
I want to convert date from 2005-04-15 00:00:00 to 2005-04-15 or 2005/04/15.
My destination table in oracle is Date. In datastage it is a Timestamp
Can anybody help?
What you posted is easy as it's exactly what it needs to look like for an Oracle DATE field declared as a Timestamp in DataStage:
So for that just send it on through - no conversion needed.
For something coming in like '2005/04/15', it's just a matter of rearranging the bits using Iconv and Oconv and then sticking the zeroes on the end:
Something like that should work.
Code: Select all
YYYY-MM-DD HH24:MI:SS
For something coming in like '2005/04/15', it's just a matter of rearranging the bits using Iconv and Oconv and then sticking the zeroes on the end:
Code: Select all
OConv(Iconv(YourField,"D/YMD"),"D-YMD[4,2,2]") : " 00:00:00"
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The OCI stage automatically puts a TO_DATE() on DATE datatype columns and your dates HAVE TO BE IN THE STRING FORM OF YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS. So, why do you want to make things not work right and prolong this post?
If you want your answer, even if it's not what you need to do, just use string parsing to juggle the fields around or use OCONV(ICONV(yourdate, "yourdateformat"), "newdateformat") type logic. Look in your DS BASIC manual or search this forum. I'm sure from Craigs post here you can figure out what it should be.
If you want your answer, even if it's not what you need to do, just use string parsing to juggle the fields around or use OCONV(ICONV(yourdate, "yourdateformat"), "newdateformat") type logic. Look in your DS BASIC manual or search this forum. I'm sure from Craigs post here you can figure out what it should be.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Thanks guys,
My be i did not ask the question right
I dont have a date field in my source file, i am just creating an extra field in the table using the transformer.
I need to put in today's date or any given date in the format YYYY-MM-DD or YYYY/MM/DD
I was using DSJOBSTARTDATE for this purpose, and getting the output in the oracle table in the format YYYY-MM-DD HH24:MI:SS.
May be i am doing something wrong !!!
How do i get a date in the format YYYY/MM/DD in to an oracle table?
My be i did not ask the question right
I dont have a date field in my source file, i am just creating an extra field in the table using the transformer.
I need to put in today's date or any given date in the format YYYY-MM-DD or YYYY/MM/DD
I was using DSJOBSTARTDATE for this purpose, and getting the output in the oracle table in the format YYYY-MM-DD HH24:MI:SS.
May be i am doing something wrong !!!
How do i get a date in the format YYYY/MM/DD in to an oracle table?
Okay, one last time. How are you viewing your result in the Oracle table? Are you browsing with DS's view data button?
You do realize that Oracle stores dates in an internal integer value and that it is up to the device querying the data to format it in a manner you like? Therefore, DS puts a TO_CHAR(xxx, 'YYYY-MM-DD HH24:MI:SS') on all DATE fields in the SQL when it queries the database. If you're using Toad or Embarcadero to browse the data, you can configure those tools to display the date however you like.
It seems that you are loading the data successfully, but when looking at the data you're not seeing slash separated data like you thought you loaded.
You do realize that Oracle stores dates in an internal integer value and that it is up to the device querying the data to format it in a manner you like? Therefore, DS puts a TO_CHAR(xxx, 'YYYY-MM-DD HH24:MI:SS') on all DATE fields in the SQL when it queries the database. If you're using Toad or Embarcadero to browse the data, you can configure those tools to display the date however you like.
It seems that you are loading the data successfully, but when looking at the data you're not seeing slash separated data like you thought you loaded.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
What if I wanted to convert from a Timestamp type with millisecondschulett wrote:What you posted is easy as it's exactly what it needs to look like for an Oracle DATE field declared as a Timestamp in DataStage:
So for that just send it on through - no conversion needed.Code: Select all
YYYY-MM-DD HH24:MI:SS
For something coming in like '2005/04/15', it's just a matter of rearranging the bits using Iconv and Oconv and then sticking the zeroes on the end:
Something like that should work.Code: Select all
OConv(Iconv(YourField,"D/YMD"),"D-YMD[4,2,2]") : " 00:00:00"
Code: Select all
YYYY-MM-DD HH24:MI:SS.000
Code: Select all
YYYY-MM-DD
Code: Select all
YYYY-MM-DD HH24:MI:SS