Link to home
Start Free TrialLog in
Avatar of Daniish
DaniishFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Michael701
Michael701
Flag of United States of America image

use
strtotime and date functions
date

echo date("d-M-Y", strtotime($variable));
Avatar of David S.
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
Avatar of Daniish

ASKER

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

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

Avatar of Daniish

ASKER

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

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.
Avatar of Daniish

ASKER

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!
ASKER CERTIFIED SOLUTION
Avatar of David S.
David S.
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Daniish

ASKER

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?
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;


Avatar of Daniish

ASKER

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).
I don't see an error in Firefox 3.
Avatar of Daniish

ASKER

I'm using FF2.

Do you see any red pushpin markers on the map ?
I see the error in FF2, but don't see what is causing it.
Avatar of Daniish

ASKER

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.
Avatar of Daniish

ASKER

I suspect your code is good and the problem lies elsewhere in my own code