0

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

msbyuva
  • 3,467
  • 13
  • 63
  • 87

1 Answers1

1

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));

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.

EvilBob22
  • 732
  • 5
  • 12
  • Date types have no format. What you posted works only because the field was implicitly converted to a string using a format specified by CR. A *far* better solution would be to use the proper formatting function from the start, eg `ToText` as shown in [this question](http://stackoverflow.com/questions/20548852/how-to-convert-datetime-object-to-string-in-crystal-reports) – Panagiotis Kanavos Jul 26 '16 at 15:34