Convert a date and time into a MS SQL select query using SelectParameters

Question

Hi all,

I have this situation where I have a SqlDatasource control and the select query is like:

SELECT col1, col2 FROM table1 WHERE colDate = @date

The source of @date is a label with the text: 2009-05-29 12:06:00 I get the following error when I run the query:

Conversion failed when converting date and/or time from character string

I tried using convert(datetime, @date), as well as different date/time formatting of the label itself. Everytime I get the error.

However, when I run the query in the management studio like:

    SELECT col1, col2 FROM table1 WHERE colDate = '2009-05-29 12:06:00'

it works like a charm!

Does anyone have any idea what I'm missing?

EDIT:

I found out that the @date is parsed as 05-29-2009 01:30:00 TT I don't know where the TT is coming from? And I'm sure SQL Server wouldn't be able to handle it?

Answer 1

You may try

SELECT col1, col2 FROM table1 WHERE colDate = CONVERT(DATETIME, @date, 120)

And try:

<SelectParameters>
  <asp:ControlParameter ControlID="label1" Name="date" PropertyName="Text" Type="DateTime" />
</SelectParameters>

Answer 2

Please try:

SELECT col1, col2 FROM table1 WHERE colDate = convert(datetime, @date,120)

For a complete listing of the available conversion formats with respect to the datetime data type, please refer to the following SQL Server Books Online Reference:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 3

Found the problem, I had a Now.ToString("MM/dd/yyyy hh:mm:ss TT") which added the double T's to the @date... Runs perfectly now!

Thanks all!