John,
> Ok, I did something stupid and now I am paying the
> price...
=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
> 200411322
=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]
