List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 18 2005 11:42pm
Subject:Re: String Formatting
View as plain text  
Daniel Kasak <dkasak@stripped> wrote on 12/18/2005 06:14:56 
PM:

> Greetings.
> 
> I've got some time data that is in an unfortunate format - stripped of
> colons and leading zeros, eg:
> 
> 00:00:10 is simply:
> 10
> 
> and
> 00:02:00 is:
> 200
> 
> Nice, eh? Until today, I assumed that I had *seconds*, but apparently
> not ...
> 
> Is there a formatting function that allows me to apply a formatting
> template, eg:
> 
> string_format( my_string, 'dd:dd:dd )
> 
> d is 'digit' ... I'm clearly making this up as I go here. I've checked
> the online docs, and I've found 'format' but it only seems to be for
> numeric data.
> 
> Any ideas? Or do I have to piece together a big combo statement with
> lots of if(), length(), locate() etc?
> 
> -- 
> Daniel Kasak
> IT Developer
> NUS Consulting Group
> Level 5, 77 Pacific Highway
> North Sydney, NSW, Australia 2060
> T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> email: dkasak@stripped
> website: http://www.nusconsulting.com.au
> 
> 
You may not be as bad off as you think you are.  One of the ways to write 
datetime information in MySQL is yyyymmddhhMMss so if you left-padded with 
zeroes each of your numbers to 14 total places, then inserted it into a 
datetime field (or fed it to a function that took datetime information 
like DATE_FORMAT()) it should be re-rendered into all of it's original 
datetime componenents.

Read this for details:
http://dev.mysql.com/doc/refman/4.1/en/datetime.html
and this for ways to work with date and time information:
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
String FormattingDaniel Kasak19 Dec
  • Re: String FormattingSGreen19 Dec
    • Re: String FormattingDaniel Kasak19 Dec