Tmestamp to date conversion

senthilt1 · Post by **senthilt1** » Tue Jun 29, 2010 4:10 am

HI All,

I want to convert the timestamp inp date to date type in output with the following format,

Inp:

2010-02-10 02:30:40

Ouput i Need with date as datatype:

20100210

Using functions Timestamptodate and datetostring, i got 20100210 in the output but as string, when i give the datatype Date it includes hypen '-' in the date.

Can you please suggest your view on this.

Thanks,
Senthil P

ArndW · Post by **ArndW** » Tue Jun 29, 2010 4:16 am

Binary data types such as numbers, dates and timestamps have no internal format. Formatting is applied only when converting from another data type on input or output.

So the important question is what is your output (sequential file or database) and what is the datatype defined as in the target. Note that a text file doesn't have a Date or Time datatype but uses strings only and thus an implicit conversion from your internal datatype to the display one is done and you can specify your "YYYYMMDD" on output.

senthilt1 · Post by **senthilt1** » Tue Jun 29, 2010 4:22 am

Thanks ArndW,

My target is database and the datatype we need is DATE. But the value to this should be like "20100210" without hypen.

Is this possible?

ArndW · Post by **ArndW** » Tue Jun 29, 2010 4:23 am

No, this is not possible. A database DATE column has no format.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jun 29, 2010 4:29 am

Date datatype is internally represented in each database.

What you mentioned is the display format. Nothing to do with load.

senthilt1 · Post by **senthilt1** » Tue Jun 29, 2010 4:33 am

Ok, Thanks for the confirmation.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 29, 2010 4:45 am

The apparent format of dates, times and timestamps is determined by default via settings in the Administrator client. These can be overridden in jobs and even in functions such as StringToDate().