something about date

sylvan_rydes · Post by **sylvan_rydes** » Wed Apr 19, 2006 4:32 pm

That was the only reason behind it. Thanks.

Now can you help me to figure out a way So I can upload a constant string type in date type OCI stage.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 19, 2006 4:49 pm

kcbland wrote:Day 0 in the DS BASIC internal date world is 1969-12-31. Somehow you're getting to there in your manipulations.

1967-12-31

chulett · Post by **chulett** » Wed Apr 19, 2006 8:24 pm

sylvan_rydes wrote:Now can you help me to figure out a way So I can upload a constant string type in date type OCI stage.

You just... do it.

Make sure it is in the format the datatype is expecting. For example, declared as a Timestamp it would need to be in the format YYYY-MM-DD HH24:MI:SS to 'upload' correctly, assuming you are using the generated sql.

avi21st · Post by **avi21st** » Wed Apr 19, 2006 10:28 pm

chulett wrote:
sylvan_rydes wrote:Now can you help me to figure out a way So I can upload a constant string type in date type OCI stage.
You just... do it. Make sure it is in the format the datatype is expecting. For example, declared as a Timestamp it would need to be in the format YYYY-MM-DD HH24:MI:SS to 'upload' correctly, assuming you are using the generated sql.

You should do a TO_CHAR(column_name,YYYY-MM-DD HH24:MI:SS') while selecting date field. Also dont key in metadata definition-import it. Then in user defined query in OCI stage use this TO_CHAR.

I hope it helps.

chulett · Post by **chulett** » Thu Apr 20, 2006 5:50 am

There's absolutely no reason to use User Defined SQL to do that - the stage will automatically generate the 'proper' TO_CHAR (and the corresponding TO_DATE) sql for you based on the datatype.

IMHO, user defined sql should be avoided at all costs, only when you've got no other choice should you fall back to it. Too many opportunities for error with it and you increase the maintenance workload of the job that uses it.