datetime conversion error

Question

datetime conversion error

Mh-397891

SSCertifiable

Points: 6704
More actions
May 4, 2009 at 10:53 am

#129761

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Take a look at the IsDate() function. If you select the rows where that = 0, for the column, you'll find the ones that won't work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 2

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Did you try this:

where

isdate(LTRIM(RIGHT(t1.sent_info, 19)))= 0

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 4

I am getting this error if I do that way:

The multi-part identifier "sent_info" could not be bound.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Mh (5/4/2009)
I am getting this error if I do that way:
The multi-part identifier "sent_info" could not be bound.

Please post the complete query.

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 6

Sorry my bad, I know why I was getting the error:

multi-part identifier could not be bound.

Thanks!

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Mh (5/4/2009)
Sorry my bad, I know why I was getting the error:
multi-part identifier could not be bound.
Thanks!

Two way street, please... tell us what you found! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)