> 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
> 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:


=this will separate the days neatly, but all of the records for a particular day will 
be in a non-predictable

=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...


PHP Database Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to