Generic Stage Option Value

irajasekharhexa · Post by **irajasekharhexa** » Tue Oct 02, 2007 10:52 am

Hi I has some difficulty in understanding the following statement which was used in one of the Job.

YR and MNTH values are paasing from the Teradata operator.

cast( (cast(YR as INTEGER)-1900)*10000 +cast(Mnth as INTEGER)*100 +cast('01' as INTEGER) as DATE)

Can any body please expain the output of the above statement?

Regds

ray.wurlod · Post by **ray.wurlod** » Tue Oct 02, 2007 2:10 pm

It's creating a date in %yyyy%mm01 format (the first of the month of the received year and month), as an integer, but with a modified century indicator. Is this, perhaps, intended for a JD Edwards data table?

irajasekharhexa · Post by **irajasekharhexa** » Wed Oct 03, 2007 5:29 am

ray.wurlod wrote:It's creating a date in %yyyy%mm01 format (the first of the month of the received year and month), as an integer, but with a modified century indicator. Is this, perhaps, intended for a JD Edwards data table?

Hi Ray,

It's not from the JDE data table.

But I didn't understand how exactly it works.

Can you please explain me how it converts into the %yyyy%mm01 format.

Regds

ray.wurlod · Post by **ray.wurlod** » Wed Oct 03, 2007 2:08 pm

Imagine we're processing the date 2007-10-04. The year is 2007, the month is 10, and we require 2071001 as the result. The "2" is the century designator.

cast(YR as INTEGER)-1900)*10000 returns (2007-1900) * 10000, or 2070000
cast(Mnth as INTEGER)*100 returns (10 * 100), or 1000
cast('01' as INTEGER) returns 1

Adding these three together is (2070000 + 1000 + 1) = 2071001

A CAST is applied to convert that string to a date. This would need to be a database with a very specific date picture, such as the structure that JDE uses.

irajasekharhexa · Post by **irajasekharhexa** » Thu Oct 04, 2007 5:50 am

ray.wurlod wrote:Imagine we're processing the date 2007-10-04. The year is 2007, the month is 10, and we require 2071001 as the result. The "2" is the century designator.

cast(YR as INTEGER)-1900)*10000 returns (2007-1900) * 10000, or 2070000
cast(Mnth as INTEGER)*100 returns (10 * 100), or 1000
cast('01' as INTEGER) returns 1

Adding these three together is (2070000 + 1000 + 1) = 2071001

A CAST is applied to convert that string to a date. This would need to be a database with a very specific date picture, such as the structure that JDE uses.

Thanks Lot Ray.

Now I can go through the statements of my jobs with more clarity

Regds