generate Timestamp
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 am
generate Timestamp
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
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
Sam
Create a user defined function which will read a parameter and based on the parameter it will format the timestamp accordingly.
Cheers
Create a user defined function which will read a parameter and based on the parameter it will format the timestamp accordingly.
Cheers
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 85
- Joined: Fri Jun 04, 2004 2:30 am
- Location: Melbourne, Australia
- Contact:
Code: Select all
Oconv(Date(), "D-YMD[4,2,2]") : " " : Oconv(Time(), "MTS")
Cheers,
Dave Nemirovsky
Dave Nemirovsky
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 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
.
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
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
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 am
Thanks Craig, I did figure out by searching through the forum. I used
And this works perfectly.
SAM
Code: Select all
CurrTime = Time()
OCONV(DATE(), "D/YMD[4,2,2]") : " " : OCONV(CurrTime, "MTS:") : ".":field(CurrTime,".",2)
SAM
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 am
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
Happy Thanksgiving and Thanks.
Sam
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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: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.
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 85
- Joined: Fri Jun 04, 2004 2:30 am
- Location: Melbourne, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 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
Thanks for the reply, BTW it was your first reply to my post on the forum.. :D
Sam
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
From the HELP.
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.
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
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.