String to date conversion not possible.

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)

String to date conversion not possible.

Author

Topic

panthagani
Yak Posting Veteran

58 Posts

Posted - 2008-02-25 : 02:26:31

If I run the below statement on EM-TAL-DB1 server, it gives me proper result. Same statement run on EM-TAL-DB2 server says:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Please help me understand why it runs on one SQL server and not on the other? Any settings that I need to check? Thank you.

SELECT COUNT(1)
from [EM-TAL-DB2].MSDB.dbo.SysJobHistory A INNER JOIN [EM-TAL-DB2].MSDB.dbo.SysJobs B ON a.Job_ID = B.Job_ID
WHERE B.name = 'EM-TAL-DB1-tlMain-EM-TAL-DB1-12'AND CAST (STR(Run_Date,8) + ' ' + REPLACE(STUFF(STUFF(STR(Run_Time,6),5,0,':'),3,0,':'),' ','0') AS DATETIME)
+ CAST(REPLACE(STUFF(STUFF(STR(Run_duration,6),5,0,':'),3,0,':'),' ','0') AS DATETIME) > DATEADD (HH,-1,GETDATE()) AND RUN_STATUS in (0,2,3)

panthagani
Yak Posting Veteran

58 Posts

Posted - 2008-02-25 : 03:16:46

Some more insight...

select CAST(REPLACE(STUFF(STUFF(STR(Run_Time,6),5,0,':'),3,0,':'),' ','0') as DATETIME) from sysjobhistory --this works

select CAST(REPLACE(STUFF(STUFF(STR(Run_duration,6),5,0,':'),3,0,':'),' ','0') as DATETIME) from sysjobhistory --this does not work

Surprisingly, both the above queries work on EM-TAL-DB1 (SQL 2000 replication publisher). The second one does not work on EM-TAL-DB2 (SQL 2000 replication subscriber). However, the first query works fine on EM-TAL-DB2 as well. Not sure how this is related but thought it might help.

Thank you.

panthagani
Yak Posting Veteran

58 Posts

Posted - 2008-03-03 : 04:16:23

Would someone help me understand this *strange* behaviour? Or is it something obvious that I'm missing?... Thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-03 : 08:04:24

What do you mean by "this does not work"?
Can you post expected result?

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-03 : 08:44:50

>>
select CAST(REPLACE(STUFF(STUFF(STR(Run_duration,6),5,0,':'),3,0,':'),' ','0') as DATETIME) from sysjobhistory

What the heck are you trying to do here? Are you trying to get data for a certain date range? then use math, not string manipulation, and proper data types to determine this. Why don't you explain to us what condition or formula you are trying to apply here. You should always simply calculate a @StartDate (as a DATETIME, not a string) and/or an @EndDate (also as a DATETIME) and use a simple

WHERE Run_duration BETWEEN @StartDate and @EndDate

or

WHERE Run_duration >= @StartDate

or something like that. Never, ever, ever use string manipulation like that to do date range filters.

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-03 : 09:35:25

Always try to use proper datatypes for fields wherever possible.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources