generate Timestamp

coolkhan08 · Post by **coolkhan08** » Tue Nov 23, 2004 3:31 pm

Hi,
I need to generate timestamp in datastage and store it in SQL and also in Teradata. Which would be the best way of generating the timestamp and be consistent with the format with what we see in SQL and Teradata.
Thanks.
SAM

rasi · Post by **rasi** » Tue Nov 23, 2004 8:24 pm

Sam

Create a user defined function which will read a parameter and based on the parameter it will format the timestamp accordingly.

Cheers

chulett · Post by **chulett** » Tue Nov 23, 2004 9:36 pm

First off, forgive me if some of this is bass-akwards, it's all from memory...

Do you want a timestamp that is consistant across the entire run of the job or one that changes second by second in 'real time'? There are functions and system variables that can get the information for you, it's up to you to format it properly based on your target.

@DATE and @TIME are System Variables that give you a constant date and time during the life of the Transformer they are used in - the date and time the job started.

The Date(), Time() and TimeDate() functions are used to get the current system date and/or time, something that could change from row to row.

Check the online help for the output format of these options so that you know what you'll need to do to format them properly. Best used in a Transform, IMHO, as long as it can all be done in a single 'line' of code.

davidnemirovsky · Post by **davidnemirovsky** » Wed Nov 24, 2004 12:42 am

Code: Select all

Oconv(Date(), "D-YMD[4,2,2]") : " " : Oconv(Time(), "MTS")

Use different Oconv conversion codes to format to other desired formats.

coolkhan08 · Post by **coolkhan08** » Wed Nov 24, 2004 10:41 am

Thanks for all the replies. I used TimeDate() function and I could generate the timestamp. But i need the precison to 1/1000th sec like 2000-08-06 01:29:12.717 in SQl and in teradata too. Iam getting the timestamp like this 2004-11-24 08:53:52 using

Code: Select all

Oconv(Date(), "D-YMD[4,2,2]") : " " : Oconv(Time(), "MTS")

.
what would be the target column types for the timestamp be in SQL and Teradata and column type in datastage with the length. how do I get the precision of 1000 for the sec's in Datastage.
Regards
SAM

chulett · Post by **chulett** » Wed Nov 24, 2004 10:48 am

There is a SYSTEM call you need to make to accomplish that. Check out this post from Ray for the details.

coolkhan08 · Post by **coolkhan08** » Wed Nov 24, 2004 11:15 am

Thanks Craig, I did figure out by searching through the forum. I used

Code: Select all

CurrTime = Time() 

OCONV(DATE(), "D/YMD[4,2,2]") : " " : OCONV(CurrTime, "MTS:") : ".":field(CurrTime,".",2)

And this works perfectly.
SAM

coolkhan08 · Post by **coolkhan08** » Thu Nov 25, 2004 9:27 pm

Ray can you shed some light on generating a seconds of 6 precision in datastage and loading it to teradata or SQL. Iam right now able to acheive a degree of 3 using the above logic. I read in a previous post of generating a 8 digit random number following the seconds of the TimeDate() for uniqueness. what is your say.
Happy Thanksgiving and Thanks.
Sam

ray.wurlod · Post by **ray.wurlod** » Thu Nov 25, 2004 11:52 pm

Since I live in the Asia-Pacific region thanksgiving (in the US sense) is meaningless. But thanks for the thought.

Precision of time is only as good as the operating system and the programming language can deliver.

DataStage Engine may be able to deliver more than milliseconds, again depending on the operating system. For example:

Code: Select all

FUNCTION TimeNow(Arg1)
$OPTIONS -TIME.MILLISECOND
* Arg1 is ignored
PRECISION 14
Ans = " " : Time()
RETURN(Ans)

will return the time to the best possible precision that your system can deliver. On the AIX 5.2 system I'm currently using, the value 60632.52907105 was returned. This system has EXACTNUMERIC set to its default value of 15.

davidnemirovsky · Post by **davidnemirovsky** » Fri Nov 26, 2004 12:03 am

Out of interest I ran that code in a routine and the answer returned was: 61243.812

We are running DS 5.2 on a Windows server.

Is there an EXACTNUMERIC type parameter in the Windows version or is that an AIX specific operating system variable?

ray.wurlod · Post by **ray.wurlod** » Fri Nov 26, 2004 12:09 am

EXACTNUMERIC is in uvconfig, and so is on all platforms.

However, Windoze is one of the operating systems that can deliver time to a granularity no better than milliseconds (at least that's true for NT 4.0 which is what I still run on my laptop).

Try a function that returns the result of 2/7 to see how much numeric precision DataStage is giving you. The PRECISION declaration does affect this, of course. Anything smaller from Time() is an operating system restriction.

coolkhan08 · Post by **coolkhan08** » Fri Nov 26, 2004 2:20 am

Geez!!! forgot you were in australia. I got the value from the function as 54190.773 which was similar to the output of Time() . I guess windows cant give me more than millisecs. Also 10/3 gives 3.333333333. So what does that mean about the datastage Precison. Can you explain about it.
Thanks for the reply, BTW it was your first reply to my post on the forum.. :D
Sam

ray.wurlod · Post by **ray.wurlod** » Fri Nov 26, 2004 3:36 am

From the HELP.

Use the PRECISION statement to control the maximum number of decimal places that are output when the system converts a numeric value from internal binary format to a character string value.

Code: Select all

PRECISION expression

Note that this is a declaration, not an assignment statement. There is no "=".
The precision expression can evaluate to a number between 0 and 14 (not 0 and 9 as specified in some places).

For more information consult the DataStage BASIC manual.

coolkhan08 · Post by **coolkhan08** » Fri Nov 26, 2004 11:13 am

Guess I have to RTFM. Thanks!