Convert jQuery Datepicker Format to SQL Date Format

by

Firstly you’ll need to stop storing the dates in D d.m format, the queries aren’t going to be able to sort based on that data.

As wyrfel pointed out, you’ll need to use the alternate field option to have two fields, one that shows the pretty(or your chosen) date format, and another that holds the value you store in the DB(in a format that the queries can sort on correctly, like yy-mm-dd).

Based on the one line of code you posted, give this a shot..

PHP/HTML: (to replace the code you posted)

<?php 
$date_valid = false;
if( isset( $_POST[$result->field_name] ) ) {
    $date_parts = explode( '-', $_POST[$result->field_name] );
    $date_valid = ( 3 == count( $date_parts ) ) 
        // Validate date - checkdate returns true/false
        ? checkdate( $date_parts[1], $date_parts[2], $date_parts[0] ) 
        : false;
}
$display_date = ( $date_valid ) ? date( 'D d.m', strtotime( implode( '-', $date_parts ) ) ) : '';
$storage_date = ( $date_valid ) ? $_POST[$result->field_name] : '';
$req =  $result->field_req ? ' required' : '';
?>
<input type="text" value="<?php echo $display_date; ?>" class="datepicker<?php echo $req ?>" />
<input name="<?php echo $result->field_name; ?>" type="hidden" value="<?php echo $storage_date; ?>" class="date_alternate" />

Datepicker jQuery: (just pull what you need from this)

jQuery(function($) {
    $( ".datepicker" ).datepicker({
        dateFormat: 'D d.m',
        altField: ".date_alternate",
        altFormat: "yy-mm-dd"
    });
});

That way all your dates get stored in yy-mm-dd format, but the user sees the date in D d.m.

I tested this approach locally and was able to get the functionality needed, here’s a few screenshots of my theme options page using a date field and with your chosen date format(my code was obviously a little different to what’s above, but the approach was the same).

Selecting a date:
enter image description here
Current values are at the bottom under the “Live Fetch” section.

What you see after clicking a date in the calendar:
enter image description here

Visual and stored values:
enter image description here

I thought it might just be nice to see the code put into practice and hope that helps.. 🙂

Leave a Comment