将数据库中的nvarchar列转换或转换为DateTime [英] Casting or converting nvarchar column in database to DateTime
问题描述
我正在使用SQL Server 2008而在db中我遇到了转换或转换为datetime的问题。我实际上是试图从db中检索记录,这些记录属于ex:2013-1-1和2013-6-20,但在我的db中,dates列是nvarchar,它可以是任何格式,例如01/01/2013或者在2013年9月1日或任何可能的方式..因为它全部依赖于用户上传的excel表...意味着我在我的数据库中创建一个db表,其中包括用户上传的excel表所具有的所有列和值..所以当我默认创建一个数据库时,我正在创建数据类型为nvarchar的列。现在,我想知道将nvarchar转换为或使用nvarchar转换为日期时间格式的最佳方法我试过这个
Hi,
I am using SQL Server 2008 and in db i am facing a problem with the casting or converting to datetime. I am actually trying to retrieve records from db which fall within say for ex: 2013-1-1 and 2013-6-20 but in my db the dates column is a nvarchar and it can be in any format say 01/01/2013 or 01-Sept-2013 or any possible way.. as it is all dependent on the user uploaded excel sheet ... means i create a db table in my db which will include all the columns and values as a user uploaded excel sheet has.. so while i create a db by default i am creating columns with data type nvarchar. Now, i want to know the best way to conert or cast the nvarchar to datetime format i tried this
SELECT CAST('20130401' as DATETIME)
但如果保存日期如01/04/2013或01.04.2013
$ b,这将有效$ b
我们可以做些什么来将01-Sept-2013转换为包含SQL语句的日期时间或日期...
等待你的回复。请帮助!!
提前感谢你!!
but this will be working if date is saved like 01/04/2013 or 01.04.2013
what can we do to convert 01-Sept-2013 to datetime or date which includes words in SQL...
Waiting for your reply.Please help!!
Thanking you in advance!!
推荐答案
尝试转换(datetime,FIELD_NAME )。由于9月不是有效的剥夺,它将于2013年9月1日失效。要处理这个问题,您可能需要编写一个函数来将此字符串替换为标准的abbrivation。
对于ex convert(datetime,ReplaceFunction(FIELD_NAME))。希望它有帮助
Try convert(datetime,FIELD_NAME). It failes for 01-Sept-2013 as Sept is not a valid abrivation. To handle this you might have to write a function to replace this string to standard abbrivation.
For ex convert(datetime,ReplaceFunction(FIELD_NAME)). Hope it helps
使用转换代替转换运算符如
Use Convert instead of cast operator like
Select * from tblExcel where CONVERT(date,UploadDate,101) between '09/02/2011' and '06/29/2013'
这篇关于将数据库中的nvarchar列转换或转换为DateTime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!