ChangeTimestamptoyear

[kumar_j]

hi all,
I Want to convert my timestamp to month and year in separate columns. From which function i have to extract

thanks

[DSguru2B]

What format is your timestamp in?
Take a look at the ICONV/OCONV functions.
If your date part of timestamp is alway YYYY-MM-DD
then for year you can do

Code: Select all

LEFT(in.timestamp,4)

for month you can do

Code: Select all

OCONV(ICONV(LEFT(in.timestamp,10),"D-YMD[4,2,2]"),"DM")

[chulett]

If your 'timestamp' comes in in a consistent format, both pieces can simply be sub-stringed out.

[kumar_j]

MY DATE format is in YYYY-MM-DD
I got the month ,how to get the year part like the one u provided for month
thks in advance

What format is your timestamp in?
Take a look at the ICONV/OCONV functions.
If your date part of timestamp is alway YYYY-MM-DD
then for year you can do

Code: Select all

LEFT(in.timestamp,4)

for month you can do

Code: Select all

OCONV(ICONV(LEFT(in.timestamp,10),"D-YMD[4,2,2]"),"DM")

[DSguru2B]

Pay attention to Craig's suggestion. That will be much faster.
As far as getting the year from ICONV/OCONV, use

Code: Select all

OCONV(ICONV(LEFT(in.timestamp,10),"D-YMD[4,2,2]"),"DY") 

[rwierdsm]

Kumar,

Assuming format YYYY-MM-DD, Craig's suggestion breaks down to

Year = in.timestamp [1, 4]
Month = in.timestamp [6, 2]

Are you looking for more than that?

Rob

[kumar_j]

Thnks all,
i got the answer

[ray.wurlod]

If month might be one or two digits, the Field() function is more appropriate.

Code: Select all

Field(in.Timestamp, "-", 2, 1)

Adapt as required for different delimiter character.