DSXchange

If you have a date dimension table in your DB, this is easy:

SELECT
dateColumn
FROM DateDimTable
WHERE dateColumn BETWEEN minDate AND Maxdate
ORDER BY dateColumn

Use this stream of date values to create your target records

Carter

ISTR a condition where the length of the element declaration was too long for the DataStage import job to handle, and so it missed the terminating period '.'. In my case it was a particular problem with length of REDEFINES, as you have, try splitting the REDEFINES statement onto two lines instead of...

As has been discussed before, if you are doing date-month math, you need to define what happens in cases like this:

Jan 31, 2006 + 1 month = ???

Is it Feb 28, 2006?

What about:

Feb 28, 2006 - 1 month = ???

Is it Jan 28, 2006, or Jan 31, 2006?

Carter

I've had this a couple of times in 7.X,
re-install fixed it; but have also used 'regedit' to hack the registry.
I still don't know what combo of mouse/keystroke/moonphase causes it,
but once gone, it's gone.

Carter

Hi, I'm following everything up to where you 'generate a schema file' from the table definition. I have 7.5, how do you do this? .... Theoretically, this would enable me to add a field to this table definition, generate a schema file, save it to the OS and boom! the new field shows up in all 19 file...

Or more directly:

Ans = Oconv(Iconv(Arg1[1,10],"D/MDY"),"D4Y") - Oconv(Iconv(Arg2[1,10],"D/MDY")),"D4Y")


Carter

Considering gateleys observation about leap years, what you must do is first extract the year portion of each date, and then subtract. Also, consider how your application treats fractional year differences; do you 'round-up' or 'round-down'? As for the built-in date difference function, since DataSt...

I've also run into CRC32 collisions on large data sets. There are only 2^32 possible CRC32 values, so any dataset with more records is guaranteed to yield collisions. But the likelyhood of a single collision in a uniformly ditributed data set is 50% even with much smaller datasets. (Look up 'Birthda...

If you are trying to extract some of the digits, why not use arithmetic operations instead?
Conversion from a number to a string is usually less efficient than pure arithmetic processing.

Carter

Sounds to me as if you are looking to use the International character set, rather than plain old 8 bit ASCII.
I believe that this can be set at the server, project, and job level?
(More savvy list members, please jump in here?)

Carter

Observe that the example dates you have given are in 'dd/mm/yyyy' format ('27/10/2002'), while the format string you gave is 'mm/dd/yyyy'. Carter I'm reading a sequential file with many dates in it. ex: 27/10/2002 27/1/2002 1/12/2003 1/3/2004 I applied StringToDate(MY_DATE_STRING[1,10],"%mm/%dd...

Samu wrote:

For example: in source if i have '1050414' , i need to get it in form 'April 14,2005'

My point about the leading year was this:

If you have '1050414', convert and add 19000000, yielding 20040414.
Then convert that to 'April 14,2005'

Carter

Not to be picky, but nano seconds are .000000001 of a second.
With format YYYYMMDDhhmmss+xxxx, you are dealing with tenths of milliseconds (or hundreds of microseconds), not nanoseconds.

Nano seconds would be like YYYYMMDDhhmmss+xxxxxxxxx.

Carter

I think you will find that the year portion can be expressed as the number of years since 1900.

Thus, 2005 becomes 105, 1997 becomes 097, etc.

Knowing this may help you with any future date arithmetic.

Carter

Yeah, but beware of filenames with embedded whitespace. You have to make sure that all command line strings enclose all file/path with Double Quotes, meaning that you have to take pains to escape the DQ correctly inside DataStage. Don't get me wrong, I *like* MKS, but it can be a PITA. Since your re...