从存储过程执行结果将DateTime2转换为DateTime [英] Convert DateTime2 to DateTime from Stored Procedure Executed Result

问题描述

如何从存储过程执行结果中将DateTime2转换为DateTime？我有一个过程，它将执行动态形成的sql语句，该语句将具有各种列的输出，其中有一些datetime2数据类型。如何将datetime2更改为datetime，因为我已经将此过程指定为Crystal Report的数据源，但是Crystal Reports将datetime2转换为字符串，而string无法在报表中执行所需的逻辑。因此，我希望该过程提供datetime而不是datetime2。

How can i Convert DateTime2 to DateTime from Stored Procedure Executed Result ? I have a procedure which will execute a dynamically formed sql statement which will have an output of various columns in which there are some datetime2 data types. How can I change that datetime2 to datetime because I haev to assign this procedure as data source to a crystal report but crystal report converts datetime2 to string and string can't do required logic in report. So I want the procedure to give datetime rather than datetime2.

我想临时表可能会对我有所帮助，但不确定如何进行..请帮助..

I guess temp tables might help me but not sure how to proceed.. please help..

推荐答案

假定DateTime2字段字符串看起来像在MS SQL / ISO 8601中一样（请参见 datetime2（Transact-SQL ）： 2007-05-02T19：58： 47.1234567，则可以提取字符串的所需部分，然后使用 CDate 或 CDateTime 将其转换为日期或日期时间。

Assuming the DateTime2 field string looks like it does in MS SQL/ISO 8601 (see datetime2 (Transact-SQL)): "2007-05-02T19:58:47.1234567", you can pull out the needed parts of the string and then use CDate or CDateTime to convert them to dates or datetimes.

If IsDate(left({Results.DT2Field}, 10))
    Then
    CDate(left({Results.DT2Field}, 10));

或用于DateTime

or for DateTime

If IsDateTime(left({Results.DT2Field}, 10) + " " + mid({Results.DT2Field},12,8))
    Then
    CDateTime(left({Results.DT2Field}, 10) + " " + mid({Results.DT2Field},12,8));

上面的结果是您转换后的值。

The results of the above are your converted values.

这篇关于从存储过程执行结果将DateTime2转换为DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！