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?
SQLServer Timestamp compare to Date String
Moderators: chulett, rschirm, roy
SQLServer Timestamp compare to Date String
Dan Marshall
Alberta Education
Edmonton, AB
Alberta Education
Edmonton, AB
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!
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!
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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:
Code: Select all
If Oconv(Iconv(TheDate, "MDY"), "D-YMD[4,2,2]") > Left(TheTimeStamp,10) Then ... Else ...IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
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)
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)
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Thanks to everyone.
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!
I will pass this on to the programmer who was asking. Thanks again, on their behalf.
Hope everyone has a good day. Happy computing!
Dan Marshall
Alberta Education
Edmonton, AB
Alberta Education
Edmonton, AB