CODEWITHSUNDEEP
c#postgresqldapper
ankur
ankur

Reputation: 4733

How to pass date time format in dapper parameter for postgresSQL?

For a query on a table in PostgreSQL I am able to fetch data correctly.

var query = "Select Id,name from employee
where
joiningTime BETWEEN '{startDateTime:yyyy-MM-dd HH:mm:ss}' AND '{endDateTime:yyyy-MM-dd HH:mm:ss}'"

The data are returned correctly but since this approach is prone for SQL Injection, I want to change this to parameterized way

var query = "Select Id,name from employee
    where
    joiningTime BETWEEN '@startDateTime' AND '@endDateTime'"


var result = dbConnection.Query<Result>(query, new {startDateTime, endDateTime });

How can the format be passed still with parameters?

Upvotes: 2

Views: 11539

Answers (1)

Caius Jard
Caius Jard

Reputation: 74670

DateTimes don't have a format, they're like number (eg like 1000 can be formatted as 1000.0 or 1x10^3 etc but it's still just a thousand).

You just write the query like:

SELECT * FROM t WHERE dateCol BETWEEN @fromDate AND @toDate

Note: you don't put ' around parameter names!

And in the dapper call you put datetime typed parameters:

DateTime x = DateTime.Now.AddDays(-1);
DateTime y = DateTime.Now;
dbConnection.Query<Result>(query, new { fromDate = x, toDate = y});

If, in your database, you've made your datetime columns varchar and filled them with strings that's the first thing you should fix (make them a proper date type)..

But even if you did dothis, the advice wouldn't change:

DateTime x = DateTime.Now.AddDays(-1);
DateTime y = DateTime.Now;
dbConnection.Query<Result>(
  "SELECT * FROM t WHERE dateCol BETWEEN @fromDate AND @toDate", 
  new { 
      fromDate = x.ToString("yyyy-MM-dd HH:mm:ss"), 
      toDate = y.ToString("yyyy-MM-dd HH:mm:ss")
  }
);

You're still writing parametyers into your SQL, you're now putting formatted strings into the parameter values to match the formatted strings in the DB table. Don't do this if your table holds DATE/TIME/TIMESTAMP type columns - this is only for if you've arrange the questionable(foolish)_ situation of storing your dates as strings and are unwilling to change it (you should)

Upvotes: 5

Related Questions