How do i change the date format from data extracted from MySQL ?

Hello,

At present when i retrieve data from my MySQL database the date appears in the following format:

1992-02-07

I would like it to appear as:

07-JAN-1992

Presumably this could be stored in this format in the database but perhaps it is better just to alter the format when the data is extracted. Either way could one of you Experts please explain how this is accomplished ?


Many thanks
www.globexposure.net/index.php
DaniishAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael701Commented:
use
strtotime and date functions
date

echo date("d-M-Y", strtotime($variable));
0
David S.Commented:
Instead of using PHP's strtotime() I prefer using MySQL's UNIX_TIMESTAMP() function.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp
0
DaniishAuthor Commented:
I am using the following Javascript Calendar on a form to let users select dates:
http://www.irt.org/articles/js068/index.htm

I have altered my code so that the date is now displayed as follows:

day - month - year

However, i was hoping someone here might be able to show how my code could be modified so that the 'month' element was displayed in text rather than as an integer. For example:

01 would become JAN
02  >> FEB

and so on...
<script type="text/javascript">
function y2k(number)    { return (number < 1000) ? number + 1900 : number; }
 
var today = new Date();
var day   = today.getDate();
var month = today.getMonth();
var year  = y2k(today.getYear());
 
function padout(number) { return (number < 10) ? '0' + number : number; }
function restart() {
    document.forms.add_blog.date.value = '' + padout(day) + '-' + padout(month - 0 + 1) + '-' + year;
    mywindow.close();
}
function newWindow() {
    mywindow=open('cal.htm','myname','resizable=no,width=350,height=270');
    mywindow.location.href = 'cal.htm';
    if (mywindow.opener == null) mywindow.opener = self;
}
</script>

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

David S.Commented:
General Rule: Don't use JavaScript code from before 2002.

Try this: (you don't need the y2k function anymore, but you'll still need the newWindow function)



if(Date.prototype){
  if(!Date.monthNames)
    Date.monthNames = ['January','February','March','April','May','June',
      'July','August','September','October','November','December'];
  if(!Date.prototype.getMonthName)
    Date.prototype.getMonthName = function(len){
      return Date.monthNames[this.getMonth()].abbr(len);
    }
  if(!Date.prototype.getFullYear)            // for ancient browsers that don't 
    Date.prototype.getFullYear = function(){ // support it natively
      var y=this.getYear();
      return ((y+'').length<4)?(y+1900):y;
    }
}
 
var today = new Date();
var day   = today.getDate();
var month = today.getMonth();
var year  = today.getFullYear();
 
function padout(number) { return (number < 10) ? '0' + number : number; }
function restart() {
    document.forms.add_blog.date.value = '' + padout(day) + '-' + today.getMonthName() + '-' + year;
    mywindow.close();
}

Open in new window

0
DaniishAuthor Commented:
Hi Kravimir,

Thanks for looking at this for me. I have added your code (see complete script below) but at the moment Firebug reports the following error:

Date.monthNames[this.getMonth()].abbr is not a function
http://www.globexposure.net/edit_form/input_form.php
Line 31
<script type="text/javascript">
/* http://www.irt.org/articles/js068/index.htm - pop-up date selector*/
if(Date.prototype){
  if(!Date.monthNames)
    Date.monthNames = ['January','February','March','April','May','June',
      'July','August','September','October','November','December'];
  if(!Date.prototype.getMonthName)
    Date.prototype.getMonthName = function(len){
      return Date.monthNames[this.getMonth()].abbr(len);
    }
  if(!Date.prototype.getFullYear)            // for ancient browsers that don't 
    Date.prototype.getFullYear = function(){ // support it natively
      var y=this.getYear();
      return ((y+'').length<4)?(y+1900):y;
    }
}
 
var today = new Date();
var day   = today.getDate();
var month = today.getMonth();
var year  = today.getFullYear();
 
function padout(number) { return (number < 10) ? '0' + number : number; }
function restart() {
    document.forms.add_blog.date.value = '' + padout(day) + '-' + today.getMonthName() + '-' + year;
    mywindow.close();
}
function newWindow() {
    mywindow=open('cal.htm','myname','resizable=no,width=350,height=270');
    mywindow.location.href = 'cal.htm';
    if (mywindow.opener == null) mywindow.opener = self;
}
</script>

Open in new window

0
David S.Commented:
Oops. Sorry, I forgot it had a dependence.

if(String.prototype && !String.prototype.abbr){ // to abbreviate a word
  String.prototype.abbr = function(len){
    return ((len>1)&&(this.length>len+1))?this.slice(0,len)+'.':this;
  }
}

then you can change

today.getMonthName()

to

today.getMonthName(3)

if you want.
0
DaniishAuthor Commented:
Sorry to be a pain - could you please show me where that fits in to the code you provided me with earlier as my attempts have only resulted in more errors!
0
David S.Commented:
Here:
<script type="text/javascript">
/* http://www.irt.org/articles/js068/index.htm - pop-up date selector*/
if(String.prototype && !String.prototype.abbr){ // to abbreviate a word
  String.prototype.abbr = function(len){
    return ((len>1)&&(this.length>len+1))?this.slice(0,len)+'.':this;
  }
}
if(Date.prototype){
  if(!Date.monthNames)
    Date.monthNames = ['January','February','March','April','May','June',
      'July','August','September','October','November','December'];
  if(!Date.prototype.getMonthName)
    Date.prototype.getMonthName = function(len){
      return Date.monthNames[this.getMonth()].abbr(len);
    }
  if(!Date.prototype.getFullYear)            // for ancient browsers that don't 
    Date.prototype.getFullYear = function(){ // support it natively
      var y=this.getYear();
      return ((y+'').length<4)?(y+1900):y;
    }
}
 
var today = new Date();
var day   = today.getDate();
var month = today.getMonth();
var year  = today.getFullYear();
 
function padout(number) { return (number < 10) ? '0' + number : number; }
function restart() {
    document.forms.add_blog.date.value = '' + padout(day) + '-' + today.getMonthName(3) + '-' + year;
    mywindow.close();
}
function newWindow() {
    mywindow=open('cal.htm','myname','resizable=no,width=350,height=270');
    mywindow.location.href = 'cal.htm';
    if (mywindow.opener == null) mywindow.opener = self;
}
</script>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaniishAuthor Commented:
I think it's this line which is causing it to fail:

return ((len>1)&&(this.length>len+1))?this.slice(0,len)+'.':this;

it inserts a ' . '  (full stop) just after the month - is this necessary for the code to work or can i simply remove it?
0
David S.Commented:
Fail? Do you mean you get an error or that it simply doesn't act how you want it to?

Anyway, yes, you can change that line to this one:

return ((len>1)&&(this.length>len+1))?this.slice(0,len):this;


0
DaniishAuthor Commented:
I tried that too but the markers have stopped displaying and Firebug keeps reporting the following:

region has no properties
http://www.globexposure.net/index.php
Line 196

When i look in my MySQL database where the date is being stored, it is not being added properly and appears as:

0000-00-00

When i remove this record from my database - my site returns to normal (no errors).
0
David S.Commented:
I don't see an error in Firefox 3.
0
DaniishAuthor Commented:
I'm using FF2.

Do you see any red pushpin markers on the map ?
0
David S.Commented:
I see the error in FF2, but don't see what is causing it.
0
DaniishAuthor Commented:
Hmmm that's a bummer. Well you can have the points for your efforts, i will just have to make do with original date format - One can but try.
0
DaniishAuthor Commented:
I suspect your code is good and the problem lies elsewhere in my own code
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.