Syntax error converting datatime from character st

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Syntax error converting datatime from character st

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources