current timestamp

[harithay]

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

[ArndW]

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?

[sjhouse]

There is a transform in the sdk to do this for you call DateCurrentDateTime.

Stephen

[paddu]

i am suing
OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : ".000"


your format is little wrong. remove the double quotes , use single quotes


OCONV(DATE(), 'D4-YMD[4,2,2]') : ' ': OCONV(TIME(), 'MTS:') : '.000'

i should work now

[ray.wurlod]

There is absolutely nothing wrong with your original formulation.

Code: Select all

OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : ".000"

Why do you claim that it is wrong?

[harithay]

hi ray,

the format is right.

i need something that genearte seconds after decimal

for example like this 2005-05-19 12:03:45.256

[ArndW]

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 }}}

[harithay]

HI Arnd

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.

[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)

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.

[harithay]

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

Code: Select all

OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : " ." : FIELD(SYSTEM(12),'.',2)

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.