create table t(time varchar(20)) insert into t values('20071017') insert into t values('20001112') goselect convert(varchar(10),cast(time as datetime),120) time from tdrop table t/* time ---------- 2007-10-17 2000-11-12(所影响的行数为 2 行) */
create table t(time varchar(20)) insert into t values('20071017') insert into t values('20001112') goselect convert(varchar(10),cast(time as datetime),120) time from t /* time ---------- 2007-10-17 2000-11-12(所影响的行数为 2 行) */ select left(time,4) + '-' + substring(time,5,2) + '-' + substring(time,7,2) time from t /* time ---------- 2007-10-17 2000-11-12(所影响的行数为 2 行) */ drop table t
如果是日期型的就这样: ---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-09-03 10:00:55 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([time] datetime) insert [tb] select '20071017' union all select '20001112' --------------开始查询-------------------------- select convert(varchar(10),convert(datetime,[time],120),120) as [time] from tb----------------结果---------------------------- /* time ---------- 2007-10-17 2000-11-12(2 行受影响) */
create table test1(ttime varchar(20))
insert test1
select '20071017' union all
select '20001112'
goselect convert(datetime,ttime,120) from test1
------------------------------------------------------
2007-10-17 00:00:00.000
2000-11-12 00:00:00.000(所影响的行数为 2 行)
insert into t values('20071017')
insert into t values('20001112')
goselect convert(varchar(10),cast(time as datetime),120) time from tdrop table t/*
time
----------
2007-10-17
2000-11-12(所影响的行数为 2 行)
*/
insert into t values('20071017')
insert into t values('20001112')
goselect convert(varchar(10),cast(time as datetime),120) time from t
/*
time
----------
2007-10-17
2000-11-12(所影响的行数为 2 行)
*/
select left(time,4) + '-' + substring(time,5,2) + '-' + substring(time,7,2) time from t
/*
time
----------
2007-10-17
2000-11-12(所影响的行数为 2 行)
*/
drop table t
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-03 10:00:55
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([time] datetime)
insert [tb]
select '20071017' union all
select '20001112'
--------------开始查询--------------------------
select convert(varchar(10),convert(datetime,[time],120),120) as [time] from tb----------------结果----------------------------
/* time
----------
2007-10-17
2000-11-12(2 行受影响)
*/
将 expression 转换为数据类型 datetime 时出现算术溢出错误。