使用SelectParameters将日期和时间转换为MS SQL选择查询

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

我有这种情况，我有一个SqlDatasource控件，选择查询是这样的：

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:

@date的来源是带有文本的标签：2009-05-29 12:06:00运行查询时出现以下错误：

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.

我尝试使用convert（datetime，@ date），以及标签本身的不同日期/时间格式。每次我收到错误。

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?

我发现@date被解析为05-29-2009 01:30:00 TT我不知道TT来自哪里？而且我确定SQL Server无法处理它？

3 个解决方案

#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>

#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:

有关datetime数据类型的可用转换格式的完整列表，请参阅以下SQL Server联机丛书参考：

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

#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!

发现问题，我有一个Now.ToString（“MM / dd / yyyy hh：mm：ss TT”），它将双T添加到@date ...现在运行完美！

Thanks all!

谢谢大家！

#1