Search code examples
postgresqldappernpgsqldate-range

Postgres date range insertion using Dapper and Npgsql

I'm trying to use Dapper to insert a date range into my Postgres database, using code like this: 

string INSERT_DATE_RANGE = @"INSERT INTO datetable (daterange) VALUES('[@dateRangeStart, @dateRangeBegin]')";

.... (standard connection stuff)

var startDate = DateTime.Now;
var endDate = DateTime.MaxValue;

connection.Execute(INSERT_DATE_RANGE, new { @dateRangeStart = startDate, @dateRangeBegin = endDate });

This gives me the following error:

22007: invalid input syntax for type date: "@dateRangeStart"

I suspect this is because the parameters I'm trying to replace are inside the single quotes. If I remove the quotes however, I get a different error message:

42601: syntax error at or near "["

Any suggestions? I could just concatenate the string but I would prefer not to use that approach as it opens things up to a possible SQL injection.

Solution

  • Use the constructor function for the daterange type, i.e.:

    INSERT INTO datetable (daterange) VALUES (daterange(@dateRangeStart, @dateRangeBegin, '[]'))