current timestamp
Moderators: chulett, rschirm, roy
current timestamp
Hi all,
in the out put i need to generate current timestamp .
the format is like this
yyyy-mm-dd hh:nn:ss.x
whre x represents 3 digits of current time micro seconds
i am suing
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : ".000"
but it is wrong
how can i get this
thanks
in the out put i need to generate current timestamp .
the format is like this
yyyy-mm-dd hh:nn:ss.x
whre x represents 3 digits of current time micro seconds
i am suing
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : ".000"
but it is wrong
how can i get this
thanks
harithay,
the output from the OCONVs will be exactly as you described (except you are specifying 3 millisecond digits and your picture only has one, but I am assuming that is a typo), you can test this by pushing the column to a sequential file and viewing the data. What is probably happening is that whatever database stage you have after creating the date in this format doesn't like the formatting.
What stage are you using to write to which database and what are your default and/or session settings?
the output from the OCONVs will be exactly as you described (except you are specifying 3 millisecond digits and your picture only has one, but I am assuming that is a typo), you can test this by pushing the column to a sequential file and viewing the data. What is probably happening is that whatever database stage you have after creating the date in this format doesn't like the formatting.
What stage are you using to write to which database and what are your default and/or session settings?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is absolutely nothing wrong with your original formulation.
Why do you claim that it is wrong?
Code: Select all
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : ".000"
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.
Harithay,
the @TIME function doesn't return milliseconds, but TIME() gives milliseconds; but the OCONV doesn't support that format on output, so you could use:
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : " ." : FIELD(TIME(),'.',2)
{{{I edited an error in the command on my original post }}}
the @TIME function doesn't return milliseconds, but TIME() gives milliseconds; but the OCONV doesn't support that format on output, so you could use:
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : " ." : FIELD(TIME(),'.',2)
{{{I edited an error in the command on my original post }}}
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
HI Arnd
thanks for ur reply;
if i use above function i am getting output like this;
example; 2005-05-22 10:07:45.
But i need ouput like this
example 2005-05-22 10:07:45.235
(after decimal current time milli seconds)
what is the function to get this output format, my target database sqlserver.
ArndW wrote:Harithay,
the @TIME function doesn't return milliseconds, but TIME() gives milliseconds; but the OCONV doesn't support that format on output, so you could use:
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : " ." : FIELD(TIME(),'.',2)
{{{I edited an error in the command on my original post }}}
thanks for ur reply;
if i use above function i am getting output like this;
example; 2005-05-22 10:07:45.
But i need ouput like this
example 2005-05-22 10:07:45.235
(after decimal current time milli seconds)
what is the function to get this output format, my target database sqlserver.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Even TIME() does not give milliseconds on all platforms. On some platforms you can only do it in code, by specifying the compiler declaration $OPTIONS TIME.MILLISECOND.
Try SYSTEM(12) - if I recall correctly - this will report in milliseconds if the system is capable of providing same. Therefore should come very close to what you want. Even better would be to capture the value of SYSTEM(12) in a stage variable, when you could test if to see if there are milliseconds and retain complete control.
Try SYSTEM(12) - if I recall correctly - this will report in milliseconds if the system is capable of providing same. Therefore
Code: Select all
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : " ." : FIELD(SYSTEM(12),'.',2)
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.
thanks ray
quote="ray.wurlod"]Even TIME() does not give milliseconds on all platforms. On some platforms you can only do it in code, by specifying the compiler declaration $OPTIONS TIME.MILLISECOND.
Try SYSTEM(12) - if I recall correctly - this will report in milliseconds if the system is capable of providing same. Therefore should come very close to what you want. Even better would be to capture the value of SYSTEM(12) in a stage variable, when you could test if to see if there are milliseconds and retain complete control.[/quote]
i will try that.
quote="ray.wurlod"]Even TIME() does not give milliseconds on all platforms. On some platforms you can only do it in code, by specifying the compiler declaration $OPTIONS TIME.MILLISECOND.
Try SYSTEM(12) - if I recall correctly - this will report in milliseconds if the system is capable of providing same. Therefore
Code: Select all
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : " ." : FIELD(SYSTEM(12),'.',2)
i will try that.