Solved

Function to convert string date into a sql format date string

Posted on 2016-10-18
3
54 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 15

Expert Comment

by:WalkaboutTigger
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 109

Accepted Solution

by:
Ray Paseur earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

770 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