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?
 
David S.Connect With a Mentor 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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.