How to format date in db with php

Question

I want to change format of a date:

if (!empty($_POST['dob']) && !empty($_POST['mob']) && !empty($_POST['yob'])) {
    $yob = mysqli_real_escape_string($con, $_POST['yob']);
    $mob = mysqli_real_escape_string($con, $_POST['mob']);
    $dob = mysqli_real_escape_string($con, $_POST['dob']);
    $date = mysqli_real_escape_string($con, "$yob->$mob->$mob");
    $addtothedb = "INSERT INTO login (Dateofbirth) VALUES ('". $date . "')";          
    $result = mysqli_query($con, $addtothedb);
}

However the data I get from db is like yyyy-mm-dd whereas I would like to have it like dd-mm-yyyy.

change the signature of the table. I believe the table is where you would want to change the definition.... unless you are trying to reformat input to be acceptable to the table? — Fallenreaper, Apr 14 '15 at 18:09
at first i was thinking that was what he wanted, but im nots sure if it is accepted because of the table definition. Phoenix, give us more details? — Fallenreaper, Apr 14 '15 at 18:10
I see your using in the var `$data` that u use the var `$mob` twice and not the var `$dob` — , Apr 14 '15 at 18:16
I think this may be a duplicate of this http://stackoverflow.com/questions/12729113/inserting-date-from-form-using-php-mysql — Rick Smith, Apr 14 '15 at 18:58

eol · Answer 1 · 2015-04-14T21:23:38.863

1

You could use the http://php.net/manual/en/function.date.php function to get the desired format.

$result = date('d-m-Y', strtotime($birthdate));

edited Apr 14 '15 at 21:23

answered Apr 14 '15 at 18:19

eol

23,236
5
46
64

Thanks for adding link to documentation. – Marshall Davis Apr 14 '15 at 18:20

score 0 · Answer 2 · answered Apr 14 '15 at 18:10

0

You can do this:

$date = new DateTime($gd.'-'.$gm.'-'.$gy);
$formattedDate = $date->format('dd-mm-yyyy');
$addtothedb="INSERT INTO login (Dateofbirth) VALUES ('". $formattedDate . "')";

answered Apr 14 '15 at 18:10

hamed

7,939
15
60
114

so long as that `Dateofbirth` is defined that way, no? – Fallenreaper Apr 14 '15 at 18:11

score 0 · Answer 3 · answered Apr 14 '15 at 18:11

0

MySQL saves any Date type data in yyyy-mm-dd format. If you want it to be dd-mm-yyyy do NOT use Date data type, instead use VARCHAR(10) to save it.

Hope it helps!

answered Apr 14 '15 at 18:11

N.DeNisse

149
9

score 0 · Answer 4 · answered Apr 14 '15 at 18:29

I have 2 functions here.

The first function ( mydate($date) ) returns a date from mySQL Date format to a user readable format.

The second function (sqldate($date)) returns a date from user format to mySQL format !!!

  function mydate($date) { 
        if ($date<>NULL) {  return date ('d/m/Y', strtotime($date)); } else { return ""; } 
        }

function sqldate($date)
        {
        if (trim($date) == "") {return "";  } 
        else {
         $date = str_replace('/', '-', $date);
        return date ('Y-m-d', strtotime(str_replace('/', '-', $date)));
        }
        }

score 0 · Answer 5 · answered Mar 09 '19 at 10:10

Use form like this

<form id="form1" name="form1" method="post" action="#">
              <label for="from">From Date</label>
              <input name="from" type="date" id="from" size="1" value="<?php echo $_POST['from']; ?>" />

              <label for="to">To Date</label>
              <input name="to" type="date" id="to" size="1" value="<?php echo $_POST['to']; ?>"/>

And make script for your preferred date format

<script>
    $(function() {
        var dates = $( "#from, #to" ).datepicker({
            defaultDate: "+1w",
            changeMonth: true,
            numberOfMonths: 2,
            dateFormat: 'yy-mm-dd',
            onSelect: function( selectedDate ) {
                var option = this.id == "from" ? "minDate" : "maxDate",
                    instance = $( this ).data( "datepicker" ),
                    date = $.datepicker.parseDate(
                        instance.settings.dateFormat ||
                        $.datepicker._defaults.dateFormat,
                        selectedDate, instance.settings );
                dates.not( this ).datepicker( "option", option, date );
            }
        });
    });
    </script>

Then you can assign date to variable using $_POST Method and you got preferred date format

How to format date in db with php

5 Answers5