Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gedimazs
Starting Member
4 Posts |
 Posted - 2010-01-08 : 06:41:39
|
Hello,
I'm new here and this is my first problem with SQL 2000. After new year (2010) I'm getting this error "Syntax error converting datatime from character string". Rolling back PC time to 2009 or increasing to 2011 fix the problem, but with 2010 not working. So where is the problem? Where must I look, or it is SQL 2000 problem when "0" at the end like 2000, 2010, 2020 ? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-08 : 06:47:49
|
can you post your query together with the data that cause this error ?
KH
[spoiler]Time is always against us[/spoiler]
|
 |
|
|
gedimazs
Starting Member
4 Posts |
Posted - 2010-01-08 : 06:57:56
|
This is what I see 
And this is the procedure that giving error:
/*****************************************************************************
Name : dbo.SP_SIEGE_INFO_R
Desc : Read siege info.
@o_entryGuildInfo ??(???? ???)
Header : Total size[2] + Total count[1]
Detail : Guild name size[1] + Guild name[Max 30] + Guild master size[1] + Guild master name[Max40] +
Guild mark[2] + Guild mark background[2] + Guild mark effect[2] + Guild channel[1][N]
Object : dbo.SP_SIEGE_INFO_R
Return : @o_sp_rtn
0 : OK.
1 : ??? ???? ??
-1 : ??? ??
-2 : ???? Read ??
-3 : ???? Read ??
-4 : ???? Read ??
Ver Date Author Description
---- --------------- -------------- ------------------------------
1.0 2006-05-05(FRI) Han Ji-Wook 1. Create
1.1 2006-05-11(THU) Han Ji-Wook 1. ????? ?? ?? ??
2. ????? ?????? ??? 0?
1.2 2006-05-15(MON) Han Ji-Wook 1. ????? ???? ???? ????
(@o_dwSealRegistTime -> @o_dwDungeonRegistTime)
2. ?? ?????? ?? (@o_guild_master)
3. ?????? ?????? ??
(@o_dwRegistTimeStart, @o_dwRegistTimeEnd)
4. ???? ???? ?? (@o_byRegistTimeFlag)
5. @o_entryGuildInfo >> ?????? ??
1.3 2006-05-30(TUE) Han Ji-Wook 1. ????? ??? << 1 >> Return
1.4 2006-06-14(WED) Han Ji-Wook 1. ???? ??[10????, ????]
1.5 2006-07-31(MON) Han Ji-Wook 1. ??????????, ???????? ??
1.6 2006-08-09(WED) Han Ji-Wook 1. ??????????, ???????? ??
1.7 2006-09-05(TUE) Han Ji-Wook 1. ?? (billion_cnt, remain_tax) Data Type ??
1.8 2007-01-03(WED) Choi Ji-Hwan 1. ????? ????(@o_DeadFrontTimeInfo) ??.
1.9 2007-01-05(WED) Choi Ji-Hwan 1. ????? ???? ?? ???.
******************************************************************************/
CREATE PROCEDURE dbo.SP_SIEGE_INFO_R
@i_byChannel tinyint , --//
@o_guild_code varchar(10) OUTPUT , --//
@o_guild_name varchar(30) OUTPUT , --//
@o_guild_master varchar(40) OUTPUT , --//
@o_dwStartTime varbinary(4) OUTPUT , --//
@o_dwRegistTime varbinary(4) OUTPUT , --//
@o_dwTaxRegistTime varbinary(4) OUTPUT , --//
@o_dwDungeonRegistTime varbinary(4) OUTPUT , --//
@o_dwRegistTimeStart varbinary(4) OUTPUT , --//
@o_dwRegistTimeEnd varbinary(4) OUTPUT , --//
@o_byRegistTimeFlag varbinary(1) OUTPUT , --// (0:no-regist, 1:regist)
@o_guild_mark1 varbinary(2) OUTPUT , --// Guild mark
@o_guild_mark2 varbinary(2) OUTPUT , --// Guild mark background
@o_guild_effect varbinary(2) OUTPUT , --// Guild mark effect
@o_billion_cnt tinyint OUTPUT , --// billion count
@o_remain_tax bigint OUTPUT , --// Tax.
@o_byStartTime varbinary(1) OUTPUT , --// Start time
@o_defenderInfo varbinary(1000) OUTPUT , --// Defender info.
@o_entryGuildInfo varbinary(1000) OUTPUT , --// Guild info.
@o_deadFrontTimeInfo varbinary(1000) OUTPUT , --// Dead front time info.
@o_sp_rtn int OUTPUT --// return (0:ok)
AS
DECLARE @v_tax_bindate varbinary(4)
DECLARE @v_seal_bindate varbinary(4)
DECLARE @v_rowcnt int
BEGIN
SET @o_sp_rtn = 0
--//get dead front time info.
SET @o_DeadFrontTimeInfo = 0x000300
EXEC dbo.SP_DEADFRONT_GET_TIMEINFO @o_deadFrontTimeInfo OUTPUT
, @o_sp_rtn OUTPUT
IF LEN(@i_byChannel) < 1 OR @i_byChannel IS NULL BEGIN
SET @o_sp_rtn = -1
RETURN
END
--// dbo.Guild_Info
SET @o_guild_code = 'NODATA'
SET @o_guild_name = 'NODATA'
SET @o_guild_master = 'NODATA'
SET @o_guild_mark1 = 0x0000
SET @o_guild_mark2 = 0x0000
SET @o_guild_effect = 0x0000
--// dbo.Siege_Info
SET @o_dwStartTime = 0x00000000
SET @o_billion_cnt = 0x0000
SET @o_remain_tax = 0x00000000
SET @o_defenderInfo = 0x00000000
SET @o_dwRegistTime = 0x00000000
SET @o_dwDungeonRegistTime = 0x00000000
SET @o_dwRegistTimeStart = 0x00000000
SET @o_dwRegistTimeEnd = 0x00000000
SET @o_byRegistTimeFlag = 0x01
SET @o_byStartTime = 0x00
SET @o_entryGuildInfo = 0x00000000
SET @o_sp_rtn = -1
SET @v_tax_bindate = dbo.FN_GetTaxBinTime(GetDate())
SET @o_dwTaxRegistTime = CAST(dbo.FN_GetRemainTimeSec(GetDate(), dbo.FN_BinDateToDateTime(@v_tax_bindate)) as varbinary(4))
SELECT @o_guild_code = guild_code
, @o_guild_name = guild_name
, @o_guild_master = dbo.FN_GetGuildMasterName(guild_code)
, @o_guild_mark1 = ISNULL(CAST(guild_mark1 as varbinary(2)), 0x0000)
, @o_guild_mark2 = ISNULL(CAST(guild_mark2 as varbinary(2)), 0x0000)
, @o_guild_effect = CAST(guild_effect as varbinary(2))
FROM dbo.GUILD_INFO WITH(NOLOCK)
WHERE byState = 1
AND byChannel = @i_byChannel
SET @v_rowcnt = @@ROWCOUNT
IF @v_rowcnt < 1 BEGIN
SET @o_sp_rtn = 1
RETURN
END
IF (@@ERROR <> 0) BEGIN
SET @o_sp_rtn = -2
RETURN
END
SELECT @o_dwStartTime = dwStartTime
, @o_dwRegistTimeStart = CAST(dbo.FN_GetSiegeRemainTimeSec(dwStartTime, -13, '12', GetDate()) as varbinary(4))
, @o_dwRegistTimeEnd = CAST(dbo.FN_GetSiegeRemainTimeSec(dwStartTime, -11, '05', GetDate()) as varbinary(4))
, @o_byRegistTimeFlag = CAST(byregisttimeflag as varbinary(1))
, @o_dwRegistTime = CAST(dbo.FN_GetRemainTimeSec(GetDate(), dbo.FN_BinDateToDateTime(dwStartTime)) as varbinary(4))
, @o_billion_cnt = billion_cnt
, @o_remain_tax = remain_tax
, @o_defenderInfo = defender_info
, @o_byStartTime = CAST(CAST(SUBSTRING(CONVERT(varchar(20), dbo.FN_BinDateToDateTime(dwStartTime), 120), 12,2) as smallint) as varbinary(1))
FROM dbo.SIEGE_INFO WITH(NOLOCK)
WHERE channel_no = @i_byChannel
AND siege_tag = 'Y'
SET @v_rowcnt = @@ROWCOUNT
IF @v_rowcnt < 1 BEGIN
SET @o_sp_rtn = 1
RETURN
END
IF @@ERROR <> 0 BEGIN
SET @o_sp_rtn = -3
RETURN
END
SET @o_dwDungeonRegistTime = dbo.FN_GetDungeonRegistTime(@o_dwStartTime, GetDate())
EXEC dbo.SP_SEAL_GUILD_LIST
@i_byChannel
, @o_entryGuildInfo OUTPUT
, @o_sp_rtn OUTPUT
IF @o_sp_rtn < 0 BEGIN
SET @o_sp_rtn = -4
RETURN
END
SET @o_sp_rtn = 0
RETURN
END
GO
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-08 : 07:09:09
|
what does the following function do ?
- FN_GetTaxBinTime
- FN_GetRemainTimeSec
- FN_BinDateToDateTime
Can also post the record that cause the error ?
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
gedimazs
Starting Member
4 Posts |
Posted - 2010-01-08 : 07:16:07
|
quote:
Originally posted by khtan
what does the following function do ?
- FN_GetTaxBinTime
- FN_GetRemainTimeSec
- FN_BinDateToDateTime
Can also post the record that cause the error ?
KH
[spoiler]Time is always against us[/spoiler]
FN_GetTaxBinTime:
/******************************************************************************
? ? : dbo.FN_GetTaxBinTime
? ? : ?? ??????? ????.
? ? : select dbo.FN_GetSiegeStartTime --//??? : 0x23E52D6C
????:
Ver Date Author Description
--------- ---------- --------------- -----------------------------------
1.0 2006-05-05 Han Ji Wook 1. ????
******************************************************************************/
CREATE FUNCTION dbo.FN_GetTaxBinTime
(
@i_GetDate datetime
) RETURNS varbinary(4)
AS
BEGIN
DECLARE @v_datapart_dw int
DECLARE @v_add_day int
DECLARE @v_tax_date varchar(14)
DECLARE @v_tax_bindate varbinary(4)
SELECT @v_datapart_dw = DATEPART(dw, @i_GetDate)
SELECT @v_add_day =
CASE
WHEN @v_datapart_dw < 4 THEN 4 - @v_datapart_dw
WHEN @v_datapart_dw > 4 THEN 11 - @v_datapart_dw
WHEN @v_datapart_dw = 4 THEN 0
END
--// ???? ???? : ??? 12?
SELECT @v_tax_date = CONVERT(VARCHAR(10), DATEADD(d, @v_add_day, @i_GetDate), 112) + '235959'
SELECT @v_tax_bindate = SUBSTRING(@v_tax_date,4,1) * 100000000
+ SUBSTRING(@v_tax_date,5,2) * 1000000
+ SUBSTRING(@v_tax_date,7,2) * 10000
+ SUBSTRING(@v_tax_date,9,2) * 100
+ SUBSTRING(@v_tax_date,11,2)
IF DATALENGTH(@v_tax_bindate) <> 4
SELECT @v_tax_bindate = 0x00000000
RETURN @v_tax_bindate
END
_____________________________________________________________________
FN_GetRemainTimeSec:
/******************************************************************************
? ? : dbo.FN_GetRemainTimeSec
? ? : ????? ?? ??? ?? ????.
??? : binary (??)
????:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2006-05-07 Han Ji Wook 1. ????
******************************************************************************/
CREATE FUNCTION dbo.FN_GetRemainTimeSec (
@i_curr_date datetime,
@i_starttime datetime
) RETURNS bigint
AS
BEGIN
DECLARE @v_remain_time bigint
IF (@i_starttime IS NOT NULL) BEGIN
SELECT @v_remain_time = DATEDIFF(second, @i_curr_date, @i_starttime)
IF @v_remain_time < 1
SELECT @v_remain_time = 0
END
RETURN ISNULL(@v_remain_time, 0)
END
_____________________________________________________________________
N_BinDateToDateTime:
/******************************************************************************
? ? : dbo.FN_BinDateToDateTime
? ? : Binary Date? DateTime ???? ????.
??? :
????:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2006-05-05 Han Ji Wook 1. ????
******************************************************************************/
CREATE FUNCTION dbo.FN_BinDateToDateTime (
@i_bin_time binary(4)
) RETURNS datetime
AS
BEGIN
DECLARE @v_datetime datetime
DECLARE @v_strtime varchar(20)
SELECT @v_strtime = '200' + CAST(CAST(@i_bin_time as int) as varchar(20))
SELECT @v_strtime = SUBSTRING(@v_strtime,1,4)
+'-'+SUBSTRING(@v_strtime,5,2)
+'-'+SUBSTRING(@v_strtime,7,2)
+' '+SUBSTRING(@v_strtime,9,2)
+':'+SUBSTRING(@v_strtime,11,2)
SELECT @v_datetime = CAST(@v_strtime as datetime)
RETURN @v_datetime
END
|
|
|
|
gedimazs
Starting Member
4 Posts |
Posted - 2010-01-09 : 04:47:10
|
Sorry for double posting, but wanted to know if it is possible to solve my problem.
Edited: Problem solved, please close this thread |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-12 : 01:34:07
|
quote:
Originally posted by gedimazs
Sorry for double posting, but wanted to know if it is possible to solve my problem.
Edited: Problem solved, please close this thread
Post the workable code
Madhivanan
Failing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Syntax error converting datatime from character st