I have the following code:
我有以下代码:
"SELECT top 1 * FROM CensusFacility_Records WHERE Division_Program = 'Division 1' AND JMS_UpdateDateTime = '" & date & "'"
The date format in column JMS_UpdateDateTime is: 8/22/2013 12:00:07 AM How can I make sure that my "date" in the query is converted to the correct time format?
JMS_UpdateDateTime中的日期格式为:8/22/2013 12:00:07 AM如何确保查询中的“日期”转换为正确的时间格式?
My date variable in my SQL query is a real/date time. I would like for it to match the format within the JMS_UpdateDateTime field.
我的SQL查询中的日期变量是一个真实/日期时间。我希望它与JMS_UpdateDateTime字段中的格式相匹配。
2 个解决方案
#1
1
If your database table is small enough, you can cast the value to an actual datetime inside your query, and pass the value in as a datetime parameter, so you're comparing dates instead of comparing strings.
如果您的数据库表足够小，您可以将该值转换为查询中的实际datetime，并将值作为datetime参数传递，因此您将比较日期而不是比较字符串。
If your table has a decent amount of records, don't do this, because it will be a performance hog.
如果您的表有相当数量的记录，请不要这样做，因为它将是性能独占。
SELECT top 1 *
FROM CensusFacility_Records
WHERE Division_Program = 'Division 1'
AND cast(JMS_UpdateDateTime as datetime) = @dateParam
I believe SQL Server will be able to read the string that's in your database and automatically cast it properly, assuming your server settings are standard.
我相信SQL Server将能够读取数据库中的字符串，并自动将其转换为正确的方式，假定您的服务器设置是标准的。
But in any case, use parameterized SQL instead of passing in a string like you've got.
但是在任何情况下，使用参数化SQL，而不是像您这样传入一个字符串。
#2
1
The format of your SQL DateTime is actually a bit of a red herring here - it can be displayed in any way the front end (e.g. Management Studio) chooses. The important thing here is that your date variable is in an unambiguous format. With this in mind I'd recommend using the ISO 8601 date/time format, e.g. date.ToString("o") (assuming date is a DateTime).
你的SQL DateTime的格式实际上有点红鲱鱼——它可以以任何方式显示在前端(例如管理工作室)选择。这里最重要的一点是，日期变量的格式是明确的。考虑到这一点，我建议使用ISO 8601日期/时间格式，例如date. tostring(“o”)(假设日期为DateTime)。
#1
1
If your database table is small enough, you can cast the value to an actual datetime inside your query, and pass the value in as a datetime parameter, so you're comparing dates instead of comparing strings.
如果您的数据库表足够小，您可以将该值转换为查询中的实际datetime，并将值作为datetime参数传递，因此您将比较日期而不是比较字符串。
If your table has a decent amount of records, don't do this, because it will be a performance hog.
如果您的表有相当数量的记录，请不要这样做，因为它将是性能独占。
SELECT top 1 *
FROM CensusFacility_Records
WHERE Division_Program = 'Division 1'
AND cast(JMS_UpdateDateTime as datetime) = @dateParam
I believe SQL Server will be able to read the string that's in your database and automatically cast it properly, assuming your server settings are standard.
我相信SQL Server将能够读取数据库中的字符串，并自动将其转换为正确的方式，假定您的服务器设置是标准的。
But in any case, use parameterized SQL instead of passing in a string like you've got.
但是在任何情况下，使用参数化SQL，而不是像您这样传入一个字符串。
#2
1
The format of your SQL DateTime is actually a bit of a red herring here - it can be displayed in any way the front end (e.g. Management Studio) chooses. The important thing here is that your date variable is in an unambiguous format. With this in mind I'd recommend using the ISO 8601 date/time format, e.g. date.ToString("o") (assuming date is a DateTime).
你的SQL DateTime的格式实际上有点红鲱鱼——它可以以任何方式显示在前端(例如管理工作室)选择。这里最重要的一点是，日期变量的格式是明确的。考虑到这一点，我建议使用ISO 8601日期/时间格式，例如date. tostring(“o”)(假设日期为DateTime)。