?
Solved

Function to convert string date into a sql format date string

Posted on 2016-10-18
3
Medium Priority
?
102 Views
Last Modified: 2016-10-26
Helly guys,

is there any function where I can pass a stringDate and it turn it into a correct sql format string?

something like this:

strDateTOSqlDate( "18/10/2016" ) -> result in "2016-10-18"

function strDateTOSqlDate(){
 
   return xxx

}

Open in new window


this function will return the correct date string to use in a SQL query

thanks
alex
0
Comment
Question by:hidrau
3 Comments
 
LVL 16

Expert Comment

by:Darrell Porter
ID: 41848508
Built-in strtotime function should do the trick:

Please note there is a difference between using forward slash ("/") and hyphen ("-") in the strtotime() function. To quote from php.net:

Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.

$tempdate = strtotime('10/16/2003');

$sqldate = date('Y-m-d',$tempdate);

echo $sqldate;

Open in new window

1
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 41848753
You may want to get some background information on how this works.  These two articles cover the subject.

https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html

https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html

Run this script and watch the output carefully.  You can use any formatting pattern, $p, you want.  Date('c') gives the ISO-8601 representation and it seems to work correctly with any SQL.
https://iconoun.com/demo/temp_hidrau.php
<?php // demo/temp_hidrau.php
/**
 * https://www.experts-exchange.com/questions/28977154/Function-to-convert-string-date-into-a-sql-format-date-string.html
 *
 * strDateTOSqlDate( "18/10/2016" ) -> result in "2016-10-18"
 *
 * https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html
 * https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html
 */
error_reporting(E_ALL);

function strDateToSqlDate($s, $p='c')
{
    $ts = strtotime($s);
    if (!$ts)
    {
        trigger_error("Date $s is not valid", E_USER_WARNING);
        return 'INVALID';
    }
    return date($p, $ts);
}

// TEST THE FUNCTION
$old = '18/10/2016';
$new = strDateToSqlDate($old);
echo PHP_EOL . "$old == $new";

$old = '18-10-2016';
$new = strDateToSqlDate($old);
echo PHP_EOL . "$old == $new";

Open in new window

1
 
LVL 1

Author Closing Comment

by:hidrau
ID: 41861287
thanks a lot
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Laravel is the most sought after web development framework. It comes with ample amount of features that make it easy for developers to work around it. Know about its features in detail.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question