如何将varchar转换为datetime [英] How to convert varchar to datetime

问题描述

我有一个[YYYYmmdd hhmmss]格式的varchar列标题（Prod_time），我正尝试将其转换为日期时间。我需要能够从特定的天数中提取数据，并且我希望能够将varchar转换为日期时间以方便此操作。

I have a varchar column header (Prod_time) in the [YYYYmmdd hhmmss] format that I am trying to convert to datetime. I need to be able to pull data from a certain number of days, and I would like to be able to convert the varchar to datetime to facilitate this.

将varchar转换为datetime的方法？无需特殊的日期时间格式，只需进行数据类型转换即可。

Is there a way to convert varchar to datetime? No special formatting of the datetime needed, only a data type conversion.

推荐答案

您需要在此处强制输入几个字符，以便convert函数知道如何处理。我们可以很容易地使用STUFF。在给定的字符串格式的情况下可以正常工作。

You need to force a couple characters in here so that the convert function knows how to deal with this. We can use STUFF for this pretty easily. This works given the provided string format.

declare @SomeChar varchar(20) = '20170216 100903'

select CONVERT(datetime, STUFF(STUFF(@SomeChar, 12, 0, ':'), 15, 0, ':'))

如果可能的话，应该考虑将数据类型转换为日期时间。它消除了这种麻烦，还防止了无效值。

If at all possible you should consider converting the datatype to a datetime. It eliminates this kind of hassle and also prevents invalid values.

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