DSXchange

Good morning everyone. I have someone here experiencing difficulty with dates. They want to compare a timestamp from a SQLServer DB to a date string (with no time). IE: If <timestamp field> > than 'June 30 1999' then...

Anyone have a straight forward solution to handling the timestamp field to remove the 'time' portion and leave only the 'date' portion that will compare properly?

Always use a date format of YYYY-MM-DD HH24:MI:SS.000 throughout your ETL. As soon as a date comes into your job, you should make sure it's in this form. This allows you to greater than, equalities, and less than comparisons because it's a lexicographic comparison.

Since DataStage Server has no internal datatypes, you can use string manipulations. Therefore, to portion off the date, its simply LEFT(columnvalue, 10). Your lexicographic comparisons still work!

Your problem really is that the form "June 30 1999" is not actually a date, but a character string.

So you're not comparing apples with apples.

In string terminology, you're trying to compare, for example:
"1999-08-31 15:03:40.024" > "June 30 1999"

Ask yourself, what is the result of this string comparison?

So you need to get this date into a consistent format. The ISO 8601 standard specifies a good one. For example: Of course, you could turn the Oconv(Iconv...)) into a Transform, so that it is a re-usable component.

Good points Ray and Ken,

I think getting the date into a consitent format is the key, then as Ken states a "lexicographic" comparison can be made. Do beware though that the two strings -

10-Mar-2003

and

10-MAR-2003

are not the same with the first being greater than the second in a comparison. Decimal "a" is greater than "A" and this is what the comparison is really about - the ASCII or machine collating sequence.

For all those old Unix people, remember that the man pages for sort used to contain verbiage about the sort being lexicographic. Most versions of Unix now do not reference this since it was misleading.

Regards,

Michael Hester

Hi,
Another way might be:
1. when extracting the timestamp from SQL Server use the date data type instead of the timestamp for this column (this will give you the DS internal date format without the time portion).
2. convert the date you have via Iconv to DS internal date format.
3. compare the dates.

this is more clean in my opinion and no worries about lower/upper case letters.

if you don't like the scheeme changes use Iconv() to get the date in DS internal format to achieve step 1.

IHTH (I Hope This Helps)

Thank you to everyone for your valuable input and the time you take to provide it. I'm sure all of this will be of assistance.
I will pass this on to the programmer who was asking. Thanks again, on their behalf.
Hope everyone has a good day. Happy computing!