我如何...在SQL中将日期转换为datetime2 [英] How do I... Convert date to datetime2 in SQL

问题描述

在我的表值函数中，我有一个类型为date的变量。我需要将它（在where子句中）与表中列的值进行比较，其类型为datetime2。任何人都可以帮我这个，因为演员不适合我。



我尝试过：



以下是我的代码：

In my Table valued function, I have a variable of type date. I need to compare it (in a where clause) with the values of a column in a table, whose type is datetime2. Can anyone please help me with this, as the cast is not working for me.

What I have tried:

Following is my code:

select * from table1 where [Date1] >= cast(@Todate as datetime2)

此处， [Date1] 是 table1 中 datetime2 和<$ < c $ c> @Totate 的类型为 date 。

推荐答案

好的， OP已找到解决方案：

OK, OP already has found solution:

Quote：

我得到了解决方案。问题是，日期部分转换为datetime2，但时间为00:00:00。然后我按照以下方式做了，它工作了。

I got the solution. The issue was that, the date part was getting converted to datetime2, but with time as 00:00:00. Then I did in the following way, and it worked.

select *
from Table1
where [Date1] >= DATEADD(MS,-1,CAST(DATEADD(DD,1,@toDate) AS datetime2))

但我必须指出 - 作为 MSDN文档 [ ^ ]状态 - date 和之间的隐式转换datetime2 可以使用 CAST 和 CONVERT 函数来实现数据类型。



datetime2（Transact-SQL） [< a href =https://msdn.microsoft.com/en- us / library / bb677335.aspxtarget =_ blanktitle =New Window> ^ ]数据类型众所周知为现有 datetime（Transact-SQL） [ ^ ]类型具有更大的日期范围，更大的默认小数精度和可选的用户指定精度，但日期 [ ^ ]数据类型表示没有时间部分的日期。因此，从 date 到 datetime2 的隐式转换结果是时间等于零的日期！

But i have to point out that - as MSDN documentation[^] states - an implicit conversion between date and datetime2 data types is possible by using CAST and CONVERT functions.

A datetime2 (Transact-SQL)[^] data type is well known as an extension of the existing datetime (Transact-SQL)[^] type that has a larger date range, a larger default fractional precision, and optional user-specified precision, but date[^] data type represents date without time part. So, the result of implicit conversion from date to datetime2 is date with time equal to zero!

这篇关于我如何...在SQL中将日期转换为datetime2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！