用于将nvarchar数据类型的数据复制到datetime格式时间的SQL查询 [英] Sql query for copying the data of nvarchar datatype time to datetime format time
问题描述
在我的表中,一列名为starttime in varchar(50)数据类型格式。我添加了一个名为starttime1 of datetime.how的列,用于将数据从starttime复制到starttime1 ..
你能帮忙吗?我写信查询。
提前感谢..
我尝试过的事情:
alter table website_Tracker alter column Starttime datetime
更新 Website_Tracker set starttime1 = CONVERT( datetime ,starttime, 101 )
update Website_Tracker
set starttime1 = convert ( datetime ,Starttime)
更新 Website_Tracker
SET starttime1 = CONVERT ( NVARCHAR ( 50 ), CONVERT ( SMALLDATETIME ,starttime, 103 ))
嗨Rakhesh,
你能发布一些开始时间
?如果格式值与SQL Server中的日期时间格式相同,那么应该没有问题。
我试图用这个脚本重现你的情况,但是它适用于我。
DECLARE @ tempTable 表(
sDate VARCHAR ( 50 ) NULL ,
dtmDate DATETIME NULL
)
INSERT INTO < span class =code-sdkkeyword> @ tempTable (sDate,dtmDate)
SELECT ' 2016-02-12 17:06:32.273', null
SELECT * FROM @ tempTable
更新 @ tempTable SET dtmDate = sDate
SELECT * FROM @ tempTable
干杯。 :)
如果您使用的是Microsoft SQL Server 2012或更高版本,请使用 TRY_PARSE [ ^ ]:
更新 Website_Tracker
SET StartTime1 = TRY_PARSE(StartTime As datetime USING ' en-US')
您还应该考虑使用 datetime2 [ ^ ]数据类型,而不是旧的datetime
类型。datetime
需要8个字节,并且仅支持1753年以后的日期。datetime2(0)
只使用6个字节,并且支持与.NETDateTime
类型相同的日期范围。
更新 Website_Tracker
SET StartTime1 = TRY_PARSE(StartTime As datetime2( 0 ) USING ' en-US')
如果你使用的是不同的DBMS,或者早于2012年的SQL Server版本,那么你需要告诉我们你在使用什么。 :)
in my table one column called starttime in varchar(50) datatype format..I added one column called starttime1 of datetime.how to copy data from starttime to starttime1..
can you please help me in writing query.
thanks in advance..
What I have tried:
alter table website_Tracker alter column Starttime datetime
update Website_Tracker set starttime1=CONVERT(datetime,starttime,101)
update Website_Tracker
set starttime1 = convert (datetime,Starttime)
UPDATE Website_Tracker
SET starttime1 = CONVERT(NVARCHAR(50),CONVERT(SMALLDATETIME, starttime,103))
Hi Rakhesh,
Can you post some value ofstarttime
? If the format value is same as datetime format in your SQL Server, there should be no problem with that.
I was trying to reproduce your case with this script, but it works for me.
DECLARE @tempTable TABLE ( sDate VARCHAR(50) NULL, dtmDate DATETIME NULL ) INSERT INTO @tempTable(sDate, dtmDate) SELECT '2016-02-12 17:06:32.273', null SELECT * FROM @tempTable UPDATE @tempTable SET dtmDate = sDate SELECT * FROM @tempTable
Cheers. :)
If you're using Microsoft SQL Server 2012 or later, use TRY_PARSE[^]:
UPDATE Website_Tracker SET StartTime1 = TRY_PARSE(StartTime As datetime USING 'en-US')
You should also consider using the datetime2[^] data type, rather than the olderdatetime
type.datetime
requires 8 bytes, and only supports dates from 1753 onwards.datetime2(0)
would only use 6 bytes, and supports the same range of dates as the .NETDateTime
type.
UPDATE Website_Tracker SET StartTime1 = TRY_PARSE(StartTime As datetime2(0) USING 'en-US')
If you're using a different DBMS, or a version of SQL Server earlier than 2012, then you'll need to tell us what you're using. :)
这篇关于用于将nvarchar数据类型的数据复制到datetime格式时间的SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!