John, > Ok, I did something stupid and now I am paying the > price...
Advertising
=boy am I glad that I'm not the only one in the world! How come the 'people' who extract these prices don't accept Mastercard? > In an internal database that tracks the time and date > something happens on our site, I mistakenly forgot to > use leading zeros in the 24 hour column(s). So, > anything that happens between 1-9 am are all short by > one digit. Now, I need to create a report that shows > these transactions in the order that they happened. > > The answer to this problem should be as simple as > adding a zero to the end of all records that are short > one digit. But, for reasons that I won't explain here, > that isn't possible. > > So, my question is this, is there a way to do a > database call that would select all the records but > allow me to order them based on the first 8 digits > only? > > If you would like an example, I need the following two > numbers to come up in the order listed. > > 20011012182929 > 2001111411322 =you don't mention the column format, so from the example data I have assumed that it is integer rather than string (or MySQL timestamp). =as you observe, if the values are sequenced according to string rules, it all works for you (left to right, byte-by-byte comparison), accordingly anything you do to cast the values as strings will solve the problem - until you get across to the hours 'columns'. =so using only the first eight character positions of all the records: SELECT * FROM tblNm ORDER BY LEFT( colNm, 8 ) =this will separate the days neatly, but all of the records for a particular day will be in a non-predictable sequence. =you could get really clever and replace the LEFT() with an IF(): if colNm contains 16 digits then use the (string) value, as is; else if colNm contains 15 digits then use a concatenation of the first 8 character-positions, a zero, and the remaining 7 character positions; else (you have more data integrity problems than your credit limit allows) =the effect of this would be to (logically) restore the missing zeros and sequence the records by day, hour, minute, and second. =Of course, it would be better to do that as an UPDATE to correct your data, and then use the 'normal' SQL query, but you did say... =thanks for this morning's brain-teaser to get the old grey cells lined up... =ok? =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
