datatype error - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

datatype error

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-05-04 : 12:55:18

I am getting the below error, on running the following sql code in our QA environment, but runs fine in the dev environment.
Please advice.

Error: Conversion failed when converting datetime from character string.

Note: data type of t1.sent_info is varchar(150), the following are the values stored in that field:

Request sent by user1 on 04/29/2009 8:49 AM
Request sent by user2 on 04/29/2009 8:59 AM
Request sent by user3 on 04/30/2009 9:01 AM
NULL

SQL code:
DECLARE
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@ThisDate DATETIME
SET @STARTDATE = null --'11/28/2008'
SET @ENDDATE = null --'12/05/2008'
SET @ThisDate = GETDATE()
IF @STARTDATE IS NULL AND @ENDDATE IS NULL
BEGIN
SET @STARTDATE = DATEADD(WK, DATEDIFF(WK, 0, @ThisDate) - 1, -1)
SET @ENDDATE = DATEADD(WK, DATEDIFF(WK, 0, @ThisDate) - 1, -1) + 6
--SELECT @STARTDATE AS [SUNDAY], @ENDDATE AS [SATURDAY]
END
SET @STARTDATE = DATEADD(Day, DATEDIFF(Day, 0, @STARTDATE), 0) --SUNDAY
SET @ENDDATE = DATEADD(Day, DATEDIFF(Day, 0, @ENDDATE+1), 0) --SATURDAY+1 -->SUNDAY

DECLARE @T TABLE (
col1 VARCHAR(100),
[DayofWeek] VARCHAR(100)
)
INSERT INTO @T
SELECT
t2.region,
DATENAME(dw, LTRIM(RIGHT(t1.sent_info, 19)))AS [DayofWeek]
FROM
table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE
t1.sent_info IS NOT NULL AND
LTRIM(RIGHT(t1.sent_info, 19)) >= @STARTDATE AND
LTRIM(RIGHT(t1.sent_info, 19)) < @ENDDATE

SELECT * FROM @T

I also tried TO CONVERT TO DATETIME which IS IN the WHERE clause, but even THEN I am getting the same err:
CAST(LTRIM(RIGHT(t1.sent_info, 19)) AS DATETIME) >= @STARTDATE AND
CAST(LTRIM(RIGHT(t1.sent_info, 19)) AS DATETIME) < @ENDDATE

Please let me know how I can resolve this error. Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-04 : 13:08:06

what does sent_info look like? For starters, try this
select sent_info from table1 where isdate(sent_info) = 0.
There's probably an empty string or ' ' in there, or some other wackyness

Jim

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-05-04 : 14:24:48

I tried where isdate(t1.sent_info) = 0, that returned all the rows, please note that the values that are stored in t1.sent_info (varchar(150) datatype) are:

Request sent by user1 on 04/29/2009 8:49 AM
Request sent by user2 on 04/29/2009 8:59 AM
Request sent by user3 on 04/30/2009 9:01 AM
NULL

Please let me know. Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-04 : 14:35:51

Did you grab just the datetime protion for the ISDATE function? Like:

SELECT
	*
FROM
	t1
WHERE
	ISDATE(LTRIM(RIGHT(t1.sent_info, 19))) = 0

EDIT Clarification.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-05-04 : 14:49:58

Yes, on doing that way, getting the below error:
The multi-part identifier "sent_info" could not be bound.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-04 : 14:57:36

Yeah, you'll need to replace T1 with your actual table name. (and you can drop the alias on the sent_info column)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources