Conversion 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

General SQL Server Forums

New to SQL Server Programming

Conversion Error

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-04-15 : 07:10:46

I have a columns with datatype varchar(50) which has date values in it.
I am trying to convert the values in the column to a date value,but i am getting conversion error.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

select last_update from importtest where convert(datetime,last_update)<convert(datetime,'24 Dec 2014')

any help would be appreciated.

thanks

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-15 : 07:33:55

It seems that there are some invalid date values. What are the actual date format?

Madhivanan

Failing to plan is Planning to fail

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-15 : 07:45:53

definitely bad dates: you can find them or work around them depending what your needs are :

IF OBJECT_ID('tempdb.dbo.#D') IS NOT NULL BEGIN DROP TABLE #D END

CREATE TABLE #D
(ID int identity(1,1), varDate varchar(50))

INSERT INTO #D
VALUES('1/1/1920'),('2/12/2154'),('2/30/1985'),('0101528'),('16AD-0'),('21/13/2005'),('12/13/1954'),('4/15/2015')

-- find bad dates
select varDate
from #D
WHERE isdate(varDate) = 0

-- work with only covertable dates
IF OBJECT_ID('tempdb.dbo.#onlydates') IS NOT NULL BEGIN DROP TABLE #onlydates END

;With OlyRealDates
AS
(
select varDate
from #D
WHERE isdate(varDate) = 1
)

SELECT * INTO #onlydates FROM OlyRealDates

SELECT * FROM #onlydates
where CAST(varDate as datetime)< CAST('12/24/2014' as datetime)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-04-15 : 08:15:15

yes,it was bad date issue.
one of the records had the value 1781.3700.

thanks for the help

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources