CODEWITHSUNDEEP
c#sqlsql-server-2008sqlparameter
Neo
Neo

Reputation: 2405

how to get the full sql query including parameters

I have recently leaped into parametrized queries in SQL. I am now getting a complaint about a date when executing the query

The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.

When I get the query I get the parametrized query string 

SELECT * FROM Table WHERE field = @p_param

What I need to get in order to debug this is

SELECT * FROM Table WHERE field = "2012-03-12 14:09:00"

How on earth do I do this? I suspect it is something simple but I just can't see it!

The datetime parameter is being added with the following:

sql2.Parameters.Add("@p_UpdateTime", SqlDbType.DateTime).Value = DateTime.Parse(updateTime);

and uptime is being set with

String updateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ff");

Upvotes: 0

Views: 2482

Answers (5)

Neo
Neo

Reputation: 2405

I have now fixed it, I was using ff for seconds when I needed to use ss

Upvotes: -1

David
David

Reputation: 1153

It looks like you're sending the string value. This is problematic due to DateTime.Format changing depending on the current thread culture.

Try something like this when passing in the parameters:

DateTime dateParam = GetDateParam();

_sqlCommand.Parameters.Add(
    new SqlParameter
    {
        DbType = DbType.Date,
        ParameterName = "@p_param",
        Value = dateParam 
    });

Upvotes: 0

RePierre
RePierre

Reputation: 9576

Open Sql Server Profiler and trace what is executed against your Sql Server. See a basic tutorial on how to use the tool.

Upvotes: 1

Pasi Savolainen
Pasi Savolainen

Reputation: 2500

Use the SQL Server Profiler (ships with all sql servers). It will show full commands sent to SQL server.

Upvotes: 1

Spiky
Spiky

Reputation: 509

You can convert your parameter like this : 

CONVERT(VARCHAR(19), @p_param, 120)

where 120 is the convert code for yyyy-mm-dd hh:mi:ss(24h).

Upvotes: 0

Related Questions