[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

Function to convert string date into a sql format date string

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
hidrau
Asked:
hidrau
1 Solution
 
WalkaboutTiggerCommented:
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
 
Ray PaseurCommented:
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
 
hidrauAuthor Commented:
thanks a lot
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now