DSXchange

Hi all,

can any one direct me about changing the date format..

my input is like:

Thu Jan 19 16:59:51 EST 2006

but i need to output as like:

YYYY-MM-DD T 00:00:00 (format)

it will be done by using iconv or oconv functions.but i am not getting reqd.output
can any one help me with exact syntax

Iconv and Oconv won't work in Parallel Stages you need to use DatetoString function. Read the manual for more help on these functions. Or post what is your syntax.

rasi wrote:Iconv and Oconv won't work in Parallel Stages you need to use DatetoString function. Read the manual for more help on these functions. Or post what is your syntax.

Shouldnt it be StringToDate

hi all,

cau any one helpme on this?

tx

hi,
Can you give us your exact requirement? Do you want your transformed data to be in string or Timestamp format?

If your input string is of the format you mentioned then you can use Stage variable and get convert "Jan" to 01(in string) and atlast concatenate all into the string you want

Regards
Om

scorpion wrote:hi all,

cau any one helpme on this?

tx

Hi

If your format "Thu Jan 19 16:59:51 EST 2006" is like this I don't think there is a function to convert. You have to break the day, month and year part based on your source format and then finally concatenate

scorpion wrote:Hi all,

Thu Jan 19 16:59:51 EST 2006

but i need to output as like:

YYYY-MM-DD T 00:00:00 (format)

it will be done by using iconv or oconv functions.but i am not getting reqd.output
can any one help me with exact syntax

Hi Scorpion,
Good news:
Like Rasi said, if the source is a string(char or varchar), you can get necessary substrings for the Date and Year directly from the Source.

Bad News:
I could not find any Pattern string that fits the Month part. The resolution would be 12 Nested If-then-else Statements in a stage variable that get the Month Number.


Finally the desired date format can be assembled from the pieces like:

SvYearPart<- <In.Date>[25,4]
SvUpCaseMonth<- UpCase(<In.Date>[5,3])
SvMonthPart<-
If SvUpCaseMonth="JAN" then "01" else
If SvUpCaseMonth="FEB" then "02" else...
SvDayPart<- <In.Date>[9,2]
SvDayOfWeekPart<- <In.Date>[1,1]
TgtDateString<- SvYearPart:"-":SvMonthPart:"-":SvDayPart:" ":SvDayOfWeekPart:" 00:00:00"

Important: The input string has to be exactly the same format every time. Leading Spaces, Variable number of digits or Characters (1 rather than 01, Thurs tather than Thu) will cause this logic to fail.

IHTH

ameyvaidya wrote:

scorpion wrote:Hi all,

Thu Jan 19 16:59:51 EST 2006

but i need to output as like:

YYYY-MM-DD T 00:00:00 (format)

it will be done by using iconv or oconv functions.but i am not getting reqd.output
can any one help me with exact syntax

Hi Scorpion,
Good news:
Like Rasi said, if the source is a string(char or varchar), you can get necessary substrings for the Date and Year directly from the Source.

Bad News:
I could not find any Pattern string that fits the Month part. The resolution would be 12 Nested If-then-else Statements in a stage variable that get the Month Number.


Finally the desired date format can be assembled from the pieces like:

SvYearPart<- <In.Date>[25,4]
SvUpCaseMonth<- UpCase(<In.Date>[5,3])
SvMonthPart<-
If SvUpCaseMonth="JAN" then "01" else
If SvUpCaseMonth="FEB" then "02" else...
SvDayPart<- <In.Date>[9,2]
SvDayOfWeekPart<- <In.Date>[1,1]
TgtDateString<- SvYearPart:"-":SvMonthPart:"-":SvDayPart:" ":SvDayOfWeekPart:" 00:00:00"

Important: The input string has to be exactly the same format every time. Leading Spaces, Variable number of digits or Characters (1 rather than 01, Thurs tather than Thu) will cause this logic to fail.

IHTH

Hi,
For dates like "2006-MAR-09" can be converted by using StringToDate("2006-MAR-09","%yyyy-%mmm-%dd").

Try this.....