将varchar日期字符串转换为datetime [英] convert varchar date string to datetime
问题描述
您好,
声明@date varchar(10)
声明@d datetime
声明@hour varchar(2)
声明@minute varchar(2)
声明@final varchar(20)
set @ date = '10 / 14/2014'
set @ hour = '16'
set @ minute = '03'
设置@final =(@ date +''+ @ hour +':'+ @ minute +':00:000')
设置@d = CONVERT(DATETIME,@ final)
打印@final
打印@d
显示转换错误
plz help
谢谢
srishti
Hello,
declare @date varchar(10)
declare @d datetime
declare @hour varchar(2)
declare @minute varchar(2)
declare @final varchar(20)
set @date='10/14/2014'
set @hour='16'
set @minute='03'
set @final=(@date+' '+@hour+':'+@minute+':00:000')
set @d =CONVERT(DATETIME, @final)
print @final
print @d
it showing error to convert
plz help
Thanks
srishti
推荐答案
您好,您应该指定转换为日期类型时使用的日期格式。
如果在变量之前添加以下行声明部分:
Hi, you should really be specifying the date format you are using when converting to date type.
If you add the following line before your variable declaration section:
SET DATEFORMAT MDY
然后阅读的msdn文档SET DATEFORMAT(Transact-SQL)声明。
希望它有所帮助。
Then have a read over the msdn documentation for SET DATEFORMAT (Transact-SQL) statement.
Hope it helps.
将脚本更改为
Change your script to this
declare @date varchar(10)
declare @d datetime
declare @hour varchar(2)
declare @minute varchar(2)
declare @final varchar(20)
set @date='10/14/2014'
set @hour='16'
set @minute='03'
set @final=(@date+' '+@hour+':'+@minute+':00')
set @d =CONVERT(DATETIME, @final)
print @final
print @d
我唯一的问题是你为什么用 VARCHAR
做日期操作?
你可以试试这样的东西,
you can try something like this,
--Date
declare @date varchar(10)
declare @d DATE
set @date='10/14/2014'
set @d = CONVERT(DATE, @date, 101) --101 the formate mm/dd/yyyy
--print @d
--Time
declare @time varchar(30)
declare @t TIME
declare @hour varchar(2)
declare @minute varchar(2)
set @hour='16'
set @minute='03'
set @time=(@hour+':'+@minute+':00:000')
set @t = CONVERT(TIME, @time, 114) -- 114 thr formate hh:mi:ss:mmm(24h)
--print @t
print @date +' ' +@time --string in total
print CAST(@d AS DATETIME)+ @t --datetime in total
了解日期时间格式
http://msdn.microsoft.com/en-us/library/ms187928 .aspx [ ^ ]
http:// www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx [ ^ ]
这篇关于将varchar日期字符串转换为datetime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!