Convert jQuery Datepicker Format to SQL Date Format

I set the jQuery Datepicker format to D d.m. displayed as Th 3.3. for a custom meta field that I want to use to sort posts. In SQL, the custom meta field is saved as D d.m.

I would like to display the D d.m. format on the front end and store it as mm-dd-yy or 03-03-2011 in my SQL database.

Any ideas?

My input field for the custom meta field cp_date:

 <input name="<?php echo $result->field_name; ?>" id="<?php echo $result->field_name; ?>" type="text" minlength="2" value="<?php if(isset($_POST[$result->field_name])) echo $_POST[$result->field_name]; ?>" autocomplete="off" class="datepicker <?php if($result->field_req) echo 'required' ?>" /> 

Solutions Collecting From Web of "Convert jQuery Datepicker Format to SQL Date Format"

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.. 🙂

please view the ‘alternate Field’ example and source code on this page. Make your alternative field a hidden one and then save the value of the alternate field rather than the visible one into your metadata.

Ahh…of course your date format for the alternate field should be ‘yy-mm-dd’.