如何将datetime转换为datetime2

Question

我有一个问题，查询目前在不同的表中的两个字段正在被用于where子句过滤数据。因为一个是datetime，另一个是datetime2，所以我得到了

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

下面是查询:

/*| 1) SELECT - ABSENCES AND SCHEDULES |*/
------------------------------------------
    -- Retrieve data of absences
DECLARE @SADate AS DATETIME2(7)
SET @SADate = '2012-12-02'
    -- Retrieve absences that have a supply teacher and are within the current date.
SELECT SAStaffID
       ,LastName + ', ' + FirstName AS FullName
       ,SADate
       ,SSPeriod
       ,SSCourseCode
       ,SSRoom
    FROM tblOCStaffAbsent -- Join the staff schedule of the absent teacher that has a supply.
    INNER JOIN tblOCStaffSchedule S
        ON tblOCStaffAbsent.SAStaffID = S.SSStaffID
           AND @SADate BETWEEN S.SSStartDt AND S.SSEndDt
    INNER JOIN tblStaff SF
        ON SF.StaffID = SAStaffID
    INNER JOIN tblUsers U
        ON U.UserID = SF.StaffUserID
    WHERE SASupplyID IS NOT NULL
        AND SADate = @SADate
--------------------------------
/*| 2) SELECT - SUPERVISIONS |*/
--------------------------------
    --Union the supervisions
UNION ALL
SELECT SSupStaffID
       ,SupervisionDt
       ,CASE WHEN SSupPeriodHalf IS NOT NULL THEN SSupPeriod + '-' + SSupPeriodHalf
             ELSE SSupPeriod
        END AS FullPeriod
       ,'(SP) ' + SSupLocation
       ,NULL AS SSupRoom
       ,SSupMIDent
    FROM tblOCStaffSupervisions
    WHERE SupervisionDt = @SADate

SADate是tblOCStaffAbsent中的datetime字段，supervision是tblocstaffmonitors中的datetime2字段。

Answer 1

来自tblocstaffmonitors表的superiondt的值可能是DateTime数据类型。@SADate = '2012-12-02'是一个字符串。@SADate应该在DateTime数据类型中设置。

Answer 2

如果你仔细看你的错误信息，这不是比较DATETIME和DATETIME2的问题，这可以由sql server完成，没有任何问题。

你有一些东西存储为字符串，但你正试图将其转换为DATETIME2。

转换为DATETIME2的操作与其他转换CONVERT (DATETIME2, MyDatetimeField)的操作相同

只知道DATETIME2的精度比DATETIME高。您的数据不会改变，只有添加了更高精度的新信息才会使用它。

看下面的例子。两行将产生相同的结果。

DECLARE @Datetime DATETIME = '03/21/2014 15:23:34.444'
SELECT @Datetime
UNION ALL
SELECT CONVERT(DATETIME2, @Datetime)

但在以下情况下

DECLARE @datetime2 DATETIME2 = SYSDATETIME();
SELECT @datetime2
UNION ALL
SELECT CONVERT(DATETIME, @datetime2)

第一次选择将比第二次选择产生更高的精度点