problem with dates loading in Oracle

chulett · Post by **chulett** » Wed Feb 28, 2007 7:20 pm

Your time transformation isn't quite right. You need to tell it that it is coming in in 12 hour format but you want it out in 24 hour format. Try this:

Code: Select all

oconv(iconv(stgvar2,"MTHS"),"MTS")

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 7:21 pm

Craig, your points are strong and valid.

paddu · Post by **paddu** » Wed Feb 28, 2007 7:34 pm

OCONV(ICONV(stgvar1,"D/MDY[2,2,4]"),"D-YMD[4,2,2]") : " " : oconv(iconv(stgvar2,"MTHS"),"MTS")

results in 2/24/2007 only .

is something wrong with my right function where i pick 10 characters 1:18:19 PM?

Craig- i even loaded to Flatfile and the result of the file is 2007-02-24

Appreciate your patience and great help

Thanks
paddu

chulett · Post by **chulett** » Wed Feb 28, 2007 8:31 pm

What happens when it's 10 PM? Let's say the time is 10:18:19 PM - what happens when you take the right 10 characters? Same with the dates - months and days can be a single digit or two digits in your format.

Better to use the field command to split it, not a fixed substring. Tell it that the field is 'space delimited' and you want the 'fields' on either side of that first space:

Code: Select all

stgvar1: Field(src_out_C_CUST_MTCH.CREATE_DATE," ",1,1)
stgvar2: Field(src_out_C_CUST_MTCH.CREATE_DATE," ",2,99)

stgvar1 gets the first field while stgvar2 gets 'all' of the fields after the first one. Give that a shot.

ps. I'd strongly suggest you try to use more descriptive stage variable names, that will help to make your derivations more 'self documenting'. stgvar1 and stgvar2 mean nothing but svTimestampDate and svTimestampTime (as an example) do.

paddu · Post by **paddu** » Wed Feb 28, 2007 11:19 pm

Craig- i will surely make all the cosmetic changes once the job works fine

chulett · Post by **chulett** » Thu Mar 01, 2007 8:21 am

Ok, fair enough. Keep in mind the fact that the job may no longer function after you make these 'cosmetic' changes.

Did the field function suggestion help?

paddu · Post by **paddu** » Thu Mar 01, 2007 1:10 pm

Craig

I tried using the field function as you suggested but the result is only date 2007-02-24 in the flatfile.

As we needed the file to be loaded in the oracle table yesterday , i used sysdate to populate into Create_date field . This is a one time job as per requirement and Create_date field is not a critical field for the business.

Business may not be interested to know why the actual Create_date values coming from Flatfile were not loaded ( as it is a Batch process timestamp) but i am very much interested to know how to solve this issue.

Any suggestions appreciated.

Thanks
paddu

chulett · Post by **chulett** » Thu Mar 01, 2007 1:36 pm

Huh... there's no reason that I can see here why the techniques posted wouldn't work as this is pretty standard timestamp work.

Can you repost (since it has been through some modifications) exactly how you are doing this now? Cut and paste from the job things like the stage variable derivations, the derivation for the flat file field you are writing to, how that field is defined and some samples of input and output data?

I'd also suggest you write the two stage variables to separate fields in the flat file as well as the 'concatenated' value for diagnostic purposes.

paddu · Post by **paddu** » Thu Mar 01, 2007 2:21 pm

Sample input Data Flatfile :

2/24/2007 1:18:19 PM Infact this the only date for all the 1050356 records from the source

stage variables used to split the date & time

svTimestampDate :Field(src_out_C_CUST_MTCH.CREATE_DATE," ",1,1)
svTimestampTime:Field(src_out_C_CUST_MTCH.CREATE_DATE," ",2,99)

Conversion used

Create_date(field)- OCONV(ICONV(svTimestampDate,"D/MDY[2,2,4]"),"D-YMD[4,2,2]") : " " : oconv(iconv(svTimestampTime,"MTHS"),"MTS")

Result of

Create_date ---- 2007-02-24
svTimestampDate--- 2/24/2007
svTimestampTime--- 1:18:19 PM

Please let me know if you need more.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 01, 2007 3:37 pm

All that's working. But when you create a timestamp, you don't want the AM/PM designator. Your derivation for Create_Date is correct, unless there's a newline in it. It must be on a single line. For example (for an ISO 8601 timestamp):

Code: Select all

Oconv(Iconv(svTimestampDate, "DMDY"), "D-YMD[4,2,2]") : " " : Oconv(Iconv(svTimestampTime, "MTHS"), "MTS")

chulett · Post by **chulett** » Thu Mar 01, 2007 3:49 pm

Which is (allegedly) exactly what he is doing.

You should also create two more stage variables and add them to the diagnostic file. We know you are parsing the input string correctly but nothing else for certain. Do the conversions in the stage variables and just the concatenation in the final derivation.

stage variables used to split the date & time

svTimestampDate :Field(src_out_C_CUST_MTCH.CREATE_DATE," ",1,1)
svTimestampTime:Field(src_out_C_CUST_MTCH.CREATE_DATE," ",2,99)
svDate: OCONV(ICONV(svTimestampDate,"D/MDY[2,2,4]"),"D-YMD[4,2,2]")
svTime: OCONV(ICONV(svTimestampTime,"MTHS"),"MTS")

Conversion used

Create_date(field): svDate : " " : svTime

Then dump all four stage variables and post them here.

paddu · Post by **paddu** » Thu Mar 01, 2007 4:52 pm

svTimestampDate-Field(src_out_C_CUST_MTCH.CREATE_DATE," ",1,1)
svTimestampTime-Field(src_out_C_CUST_MTCH.CREATE_DATE," ",2,99)
svDate- OCONV(ICONV(svTimestampDate,"D/MDY[2,2,4]"),"D-YMD[4,2,2]")
svTime-oconv(iconv(svTimestampTime,"MTHS"),"MTS")

results of four stage variables

Create_Date - 2007-02-24
svTimestampDate-2/24/2007
svTimestampTime-1:18:19 PM
svDate-2007-02-24
svTime-Blank

chulett · Post by **chulett** » Thu Mar 01, 2007 4:55 pm

Well... there ya go. Your time transformation is failing for some reason. Now just need to puzzle out the why of it...

chulett · Post by **chulett** » Thu Mar 01, 2007 5:06 pm

Ok, that wasn't so hard to figure out. Built a small testbed routine to check the conversions and found that it doesn't like the space in the time before the AM/PM part. Silly Wabbit.

Try this:

svTimestampTime: Convert(" ","",Field(src_out_C_CUST_MTCH.CREATE_DATE," ",2,99))

paddu · Post by **paddu** » Thu Mar 01, 2007 6:29 pm

Craig-Well... there ya go. Your time transformation is failing for some reason. Now just need to puzzle out the why of it...

yes that's why i asked ,was something wrong with my right function (time transformation)???in my earlier post.

I used the stage variable you suggested and
finally the results are

Create Date: 2007-02-24 13:18:19
svTimestampDate-2/24/2007
svTimestampTime-1:18:19 PM
svDate-2007-02-24
svTime-13:18:19

Craig- Thanks a lot for your patience and valuable information

Thanks everyone
Paddu