update [master].[dbo].[A] set [begintime]=convert(varchar(10),[begintime],121)+' 08:02:00'
create table test(d datetime) insert test select '2021-2-12 10:10:10' union all select '2001-3-2 10:10:10' union all select '2051-6-8 10:10:10' union all select '2008-1-28 10:10:10'DECLARE @参数 datetime set @参数='08:02:00' update test set d =CONVERT(varchar(100),d,23)+' '+@参数 select * from test /* 2021-02-12 08:02:00.000 2001-03-02 08:02:00.000 2051-06-08 08:02:00.000 2008-01-28 08:02:00.000 */
update activityplaylist set ap_date = convert(datetime,convert(varchar(20),ap_date,120)+' 20:20:20') ; error:[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'convert(varchar(20),ap_date,120)+' 20:20:20')' at line 1 update activityplaylist set ap_date = concat(convert(varchar(20),ap_date,121),' 20:20:20'); error:[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(20),ap_date,121),' 20:20:20')' at line 1 这两个都报错,,在mysql下面运行的 版本为:5.5.33 怎么弄...
本帖最后由 DBA_Huangzj 于 2014-04-30 12:30:22 编辑
帮你移到mysql版,应该很容易就解决了
update tt set dt = concat(substring(dt,1,10),' 08:02:00');
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-30 11:18:55
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([DateTimes] datetime)
insert [huang]
select '2021-2-12 10:10:10' union all
select '2001-3-2 10:10:10' union all
select '2051-6-8 10:10:10' union all
select '2008-1-28 10:10:10'
--------------生成数据--------------------------select * from [huang]
UPDATE huang SET [DateTimes]=CONVERT(VARCHAR(10),[DateTimes],120)+' 08:02:00'
select * from [huang]
----------------结果----------------------------
/*
DateTimes
-----------------------
2021-02-12 10:10:10.000
2001-03-02 10:10:10.000
2051-06-08 10:10:10.000
2008-01-28 10:10:10.000(4 row(s) affected)(4 row(s) affected)DateTimes
-----------------------
2021-02-12 08:02:00.000
2001-03-02 08:02:00.000
2051-06-08 08:02:00.000
2008-01-28 08:02:00.000
*/
update [master].[dbo].[A] set [begintime]=convert(varchar(10),[begintime],121)+' 08:02:00'
create table test(d datetime)
insert test
select '2021-2-12 10:10:10' union all
select '2001-3-2 10:10:10' union all
select '2051-6-8 10:10:10' union all
select '2008-1-28 10:10:10'DECLARE @参数 datetime
set @参数='08:02:00'
update test set d =CONVERT(varchar(100),d,23)+' '+@参数
select * from test /*
2021-02-12 08:02:00.000
2001-03-02 08:02:00.000
2051-06-08 08:02:00.000
2008-01-28 08:02:00.000
*/
error:[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'convert(varchar(20),ap_date,120)+' 20:20:20')' at line 1
update activityplaylist set ap_date = concat(convert(varchar(20),ap_date,121),' 20:20:20');
error:[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(20),ap_date,121),' 20:20:20')' at line 1
这两个都报错,,在mysql下面运行的 版本为:5.5.33 怎么弄...