date syntax is not valid, PostgreSQL

Question

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?

Your variables are empty. Before creating SQL, confirm that the variables are dates. Also use pg_query_params to parameterize the query — zedfoxus
– zedfoxus, Commented Dec 10, 2015 at 3:47
@Tamil I tried the var_dump it is : string(10) "2015-12-18". Is this the error? — Elbert
– Elbert, Commented Dec 10, 2015 at 4:30
If I remember correctly, you cannot use variables inside strings like that when using single quotes. But in either case, you should do what @zedfoxus suggests, use parameterized queries. — dan-klasson
– dan-klasson, Commented Dec 10, 2015 at 4:30

zedfoxus · Accepted Answer · 2015-12-10 05:53:28Z

1

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);
}

?>

answered Dec 10, 2015 at 5:53

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elbert Over a year ago

Thank you. It;s inspire me a lot. The input is read as a date now

Collectives™ on Stack Overflow

date syntax is not valid, PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related