CODEWITHSUNDEEP
postgresqldate
Elbert
Elbert

Reputation: 506

date syntax is not valid, PostgreSQL

I would like to query a column name's inputdate, type : date. When I query:

where (inputdate>='$VEStart' AND inputdate<='$VEEnd')

I got:

error : warning pg_query() query failed error invalid input syntax for date where (inputdate>='' AND inputdate<='').

But when I try to replace it:

where (inputdate>='2015-12-01' AND inputdate<='2015-12-31')

It works. I thought it was a problem with variables. so I tried to echo both variables, but they display the right values. Anything wrong here?

Upvotes: 0

Views: 525

Answers (1)

zedfoxus
zedfoxus

Reputation: 37129

Just to give you an example beyond the comment, use something like this and ensure that you add improvements to the below code before putting it into production use; also test it well.

<?php

$VEStart = '2015-01-01';
$VEEnd = '2015-2-28';

// validate the dates
if (!isDateValid($VEStart)) {
    print "Invalid start date\n";
    return;
}
if (!isDateValid($VEEnd)) {
    print "Invalid end date\n";
    return;
}

// format the dates
$VEStart = formattedDate($VEStart);
$VEEnd = formattedDate($VEEnd);
echo sprintf ("all good - %s and %s\n", $VEStart, $VEEnd);

// see http://php.net/manual/en/function.pg-query-params.php
$sql = 'select ... where inputdate between $1 and $2';
$connection = pg_connect('...');
$result = pg_query_params($connection, $sql, array($VEStart, $VEEnd));
...more code...    

// ----

// add good phpdoc
// see how others add doc - http://stackoverflow.com/questions/1904214/what-is-the-proper-php-function-documentation-format
function formattedDate($date) {
    list($year, $month, $day) = explode('-', $date);
    return date('Y-m-d', mktime(0, 0, 0, $month, $day, $year));
}

// add good phpdoc
function isDateValid($date) {
    list($year, $month, $day) = explode('-', $date);
    return checkdate($month, $day, $year);
}

?>

Upvotes: 1

Related Questions