cast(日期列 as datetime)有点风险,如果SQL Server所在的服务器区域语言不同,得到的结果会不一样
你的文章我看了,没看太懂。暂时就用的CAST解决的问题。可以再赐教一下么
可能你没在多语言环境实操过而已,我这边主要是爱尔兰的服务器,但是自己的机器是简体,两边的区域语言设置不同,cast不能选择格式,所以容易导致同一个语句两边得到的不同,我这里有个例子,你可以执行一下,就是模拟这种情况的: CREATE VIEW dbo.NextNewYearEve AS SELECT DATEADD(YEAR, DATEDIFF(year, '12/31/2000', CURRENT_TIMESTAMP), '12/31/2000') AS NextNewYearEve; GO
SET LANGUAGE us_english; SELECT NextNewYearEve FROM dbo.NextNewYearEve; /* Changed language setting to us_english. NextNewYearEve ----------------------- 2009-12-31 00:00:00.000 */ SET LANGUAGE Norwegian; SELECT NextNewYearEve FROM dbo.NextNewYearEve; GO /* Changed language setting to Norsk. NextNewYearEve ----------------------- Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string. */ DROP VIEW dbo.NextNewYearEve;
这样做个转换呗
DECLARE @sdate VARCHAR(20),@edate DATETIME
SET @sdate='2000-2-1 00:00:00'
SET @edate=@sdate
SELECT CONVERT(VARCHAR(20),@edate,120)
Select CONVERT(varchar(100), GETDATE(), 0)--05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1)--05/16/06
Select CONVERT(varchar(100), GETDATE(), 2)--06.05.16
Select CONVERT(varchar(100), GETDATE(), 3)--16/05/06
Select CONVERT(varchar(100), GETDATE(), 4)--16.05.06
Select CONVERT(varchar(100), GETDATE(), 5)--16-05-06
Select CONVERT(varchar(100), GETDATE(), 6)--16 05 06
Select CONVERT(varchar(100), GETDATE(), 7)--05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8)--10:57:46
Select CONVERT(varchar(100), GETDATE(), 9)--05 16 200610:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10)--05-16-06
Select CONVERT(varchar(100), GETDATE(), 11)--06/05/16
Select CONVERT(varchar(100), GETDATE(), 12)--060516
Select CONVERT(varchar(100), GETDATE(), 13)--16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14)--10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20)--2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21)--2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22)--05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23)--2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24)--10:57:47
Select CONVERT(varchar(100), GETDATE(), 25)--2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100)--05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101)--05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102)--2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103)--16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104)--16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105)--16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106)--16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107)--05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108)--10:57:49
Select CONVERT(varchar(100), GETDATE(), 109)--05 16 200610:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110)--05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111)--2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112)--20060516
Select CONVERT(varchar(100), GETDATE(), 113)--16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114)--10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120)--2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121)--2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126)--2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130)--18 ???? ?????? 142710:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131)--18/04/142710:57:49:920AM
详见我的文章:http://blog.csdn.net/dba_huangzj/article/details/7657979
你的文章我看了,没看太懂。暂时就用的CAST解决的问题。可以再赐教一下么
CREATE VIEW dbo.NextNewYearEve
AS
SELECT DATEADD(YEAR, DATEDIFF(year, '12/31/2000', CURRENT_TIMESTAMP),
'12/31/2000') AS NextNewYearEve;
GO
SET LANGUAGE us_english;
SELECT NextNewYearEve
FROM dbo.NextNewYearEve; /*
Changed language setting to us_english.
NextNewYearEve
-----------------------
2009-12-31 00:00:00.000
*/
SET LANGUAGE Norwegian;
SELECT NextNewYearEve
FROM dbo.NextNewYearEve;
GO
/*
Changed language setting to Norsk.
NextNewYearEve
-----------------------
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from
character string.
*/
DROP VIEW dbo.NextNewYearEve;