如何在SQL中将文本列转换为datetime [英] How to convert text column to datetime in SQL
问题描述
我有一个文本列:
备注(text,null)
Remarks (text, null)
是5/21/2013 9:45:48 AM
A sample value is "5/21/2013 9:45:48 AM"
如何将其转换为如下所示的日期时间格式:2013-05-21 09:45 :48.000
How do I convert it to a datetime format like this: "2013-05-21 09:45:48.000"
转换的原因是我试图在备注列中获取datetime列和日期戳之间的总时数。我在想这样的事情:
The reason for the conversion is that I was trying to get the total number of hours between a datetime column and the date stamp in the Remarks column. I was thinking of something like this:
备注(text,null) - Date_Sent(datetime,null)
Remarks (text, null) - Date_Sent (datetime, null)
要清楚的是,这些列表示日期时间,客户发送的查询(Date_Sent)和代表关于查询(响应)的最后响应,因此对于Date_Sent的样本,值为2013-05 -21 08:00:00.000,回应价值为5/21/2013 10:00:00 AM,我应该得到2.00(2小时)的值。不幸的是,在我正在处理的数据库中,备注是一个文本,Date_Sent是一个datetime。
To be clear, the columns represent the datetime an inquiry by a customer was sent (Date_Sent) and the last response made by a representative regarding the inquiry (Response), so for a sample of a Date_Sent having a value of "2013-05-21 08:00:00.000" and a Response with a value of "5/21/2013 10:00:00 AM", I should get a value of 2.00 (2 hours). Unfortunately, in the database I'm working on, Remarks is a text and Date_Sent is a datetime.
我是一个新的SQL,所以我真的需要帮助这个:( TIA!
I'm kind of new to SQL, so I really need help on this :( TIA!
推荐答案
使用样式101的转换
select convert(datetime, Remarks, 101)
如果您的列真的是文本
,则需要转换为varchar,然后转换为datetime
If your column is really text
you need to convert to varchar before converting to datetime
select convert(datetime, convert(varchar(30), Remarks), 101)
这篇关于如何在SQL中将文本列转换为datetime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!