如何在SQL Server中将字符串转换为datetime [英] How to convert string to datetime in SQL server
本文介绍了如何在SQL Server中将字符串转换为datetime的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的日期列是varchar(50),它包含混合日期,如dd / mm / yyyy和mm / dd / yyyy现在我想将日期列值转换为smalldatetime
但是它给出错误
从字符串转换日期和/或时间时转换失败。对于mm / dd / yyyy
请给出一些建议,以便我可以用smalldatetime格式转换这个混合数据
提前谢谢。
我尝试过:
SELECT转换(日期,'01 / 14 / 2017',103)
声明@tbl表
(
date varchar (20)
)
插入@tbl
选择'14 / 03/2017'
插入@tbl
select '01 / 14/2017'
从@tbl
$ b选择转换(smalldatetime,convert(varchar(10),date,103),103) $ b选择转换(smalldatetime,convert(varchar(10),date,112),103)@tbl
解决方案
假设这个table在你的控制之下,你正在尝试做一些功课或者类似的东西......永远不要在文本类型列(char,nchar,varchar,nvarchar或text)中存储日期。始终将日期存储在DATE
或DATETIME
类型的列中。将任何格式的日期数据保留到UI层,而不是从数据库中检索。
如果您对这些数据感兴趣,那么您在确定哪种格式时遇到问题使用...在任何月份的12日之后的任何日期都可以使用,因为格式DD / MM / YYYY或MM / DD / YYYY将是显而易见的(没有第13个月所以只要出现> 12值必须是日期的DAY
。您可以使用WHILE
循环处理大部分数据,手动处理的模糊数据手动
这不是我推荐的东西,但这应该适用于您指定的两个可能的组合
声明 @ tbl 表
(
[日期] varchar ( 20 )
)
插入 进入 @ tbl
选择 ' 2017年3月14日'
UNION 所有
选择 ' 01/14/2017'
SELECT CASE WHEN TRY_PARSE([ date ] AS smalldatetime ) IS NULL 那么 TRY_PARSE((SUBSTRING([ date ], 4 , 2 )+ ' /' + SUBSTRING([ date ], 1 , 2 )+ ' /' + SUBSTRING([ date ], 7 , 4 )) AS smalldatetime ) ELSE TRY_PARSE([ date ] AS smalldatetime ) END [convertedtype] FROM < span class =code-sdkkeyword> @ tbl
结果:
convertedtype
2017-03-14 00:00:00
2017-01-14 00 :00:00
我的解决方案是
SELECT转换(datetime,'2017/05/05',103)
2017-05-05 00:00:00.000
SELECT转换(日期,演员表('2017/05/05'作为日期),103)
2017-05-05
SELECT转换(datetime,'05 / 05/2017',103)
2017-05-05 00:00:00.000
SELECT转换(日期,演员表('05) / 05/2017 2017'as date),103)
-05-05
SELECT转换(smalldatetime,'2017/05/05',103)
2017-05-05 00:00:00.000
SELECT转换(smalldatetime,cast('2017/05/05'作为日期),103)
2017-05-05 00:00:00.000
I have date column which is varchar(50) and it contains mixed dates like dd/mm/yyyy and mm/dd/yyyy now i want to convert the date column values to smalldatetime
but it is giving error
Conversion failed when converting date and/or time from character string. for mm/dd/yyyy please give some suggestion so that i can convert this mixed data in smalldatetime format Thank you in advance.
What I have tried:
SELECT Convert(date,'01/14/2017',103)
declare @tbl table ( date varchar(20) ) insert into @tbl select '14/03/2017' insert into @tbl select '01/14/2017' select convert(smalldatetime ,convert(varchar(10),date,103), 103) from @tbl select convert(smalldatetime,convert(varchar(10),date,112),103) from @tbl
解决方案
Assuming this table is under your control and you are trying to do some homework or similar ... NEVER store dates in text-type columns (char, nchar, varchar, nvarchar or text). ALWAYS store dates in columns of typesDATE
orDATETIME
. Leave any formatting of the date data to the UI layer rather than when retrieving from the database.
If you are stuck with this data then you have problems determining which format to use ... it will be fine for any dates after the 12th of any month because the format DD/MM/YYYY or MM/DD/YYYY will be obvious (there is no 13th month so wherever that >12 value appears must be theDAY
of the date. You might be able to process the bulk of that data using aWHILE
loop, leaving the ambiguous data to be handled manually
Its is not something i would recomment, but this should work for the two posible combinations you specified
declare @tbl table ( [date] varchar(20) ) insert into @tbl select '14/03/2017' UNION ALL select '01/14/2017' SELECT CASE WHEN TRY_PARSE([date] AS smalldatetime) IS NULL THEN TRY_PARSE((SUBSTRING([date],4,2) + '/' + SUBSTRING([date],1,2) + '/' +SUBSTRING([date],7,4)) AS smalldatetime) ELSE TRY_PARSE([date] AS smalldatetime) END [convertedtype] FROM @tbl
Result:
convertedtype
2017-03-14 00:00:00
2017-01-14 00:00:00
hi my solution is
SELECT Convert(datetime,'2017/05/05',103)
2017-05-05 00:00:00.000
SELECT Convert(date,cast('2017/05/05' as date),103)
2017-05-05
SELECT Convert(datetime,'05/05/2017',103)
2017-05-05 00:00:00.000
SELECT Convert(date,cast('05/05/2017' as date),103)
2017-05-05
SELECT Convert(smalldatetime,'2017/05/05',103) 2017-05-05 00:00:00.000 SELECT Convert(smalldatetime,cast('2017/05/05' as date),103) 2017-05-05 00:00:00.000
这篇关于如何在SQL Server中将字符串转换为datetime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文