August 29, 2008 at 7:35 pm
Hi-
I have a column called value that is of the varchar(64) type. One type of data stored as a string is the date time, for example: 8/29/2008 10:28:39 AM
The following query produces the "Conversion failed when converting datetime from character string" error. I first filter out all non-datetime rows in the first where clause.
select value from table
where name = 'DateTime'
and cast ( value as datetime ) between cast ('2008-08-26 00:00:00' as datetime ) and cast ( '2008-08-28 23:59:59' as datetime )
What is strange is that I didn't have a problem running this same query yesterday...
Please advise. Thanks!
August 29, 2008 at 8:02 pm
August 29, 2008 at 8:41 pm
I'm not sure but I think the CAST is being done BEFORE the filtering.
Also you may just have had an invalid value inserted into that column. you can use the IsDate() function to determine which row contains an invalid date. Like:
Where IsDate(value) = 0
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 4, 2008 at 5:40 pm
I do the following:
select value from table
where name = 'DateTime'
and IsDate (value) = 0
And no rows return. This should indicate that all the string values is recognized as a valid date.
Yet, I still get the "Conversion failed when converting datetime from character string" error when executing this statement. I'm stumped. Is this a sql server bug?:
select value from table
where name = 'DateTime'
and IsDate (value) = 1
and cast ( value as datetime ) between cast ('2008-08-26 00:00:00' as datetime ) and cast ( '2008-08-28 23:59:59' as datetime )
September 4, 2008 at 5:58 pm
Try this:
declare @value varchar(64)
set @value = '2008-08-26 00:00:00'
select @value, CONVERT(datetime, @value)
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]
September 4, 2008 at 6:30 pm
My Bad. IsDate returns 1 for valid dates and 0 for invalid dates.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 4, 2008 at 7:50 pm
Thanks Jack and Jody.
Jody's example also works, and using 'convert' works on an individual basis when I pull out a row or two of data.
But when I query from the whole table, I get the conversion failed error. Any ideas?
September 4, 2008 at 8:24 pm
siweb, try this:
You didn't mention whether or not the values in the between clause are hardcoded, so based on your example I am assuming they are. This works with hard coded values.
CREATE TABLE #test
(
Name char(10),
Value varchar(60)
)
INSERT INTO #test VALUES('DateTime', '8/29/2008 10:28:39 AM')
INSERT INTO #test VALUES('DateTime', '8/27/2008 11:40:39 AM')
INSERT INTO #test VALUES('DateTime', '8/26/2008 01:45:57 PM')
--select * from #test
SELECT Value FROM #test
WHERE Name = 'DateTime'
AND CONVERT(datetime, value) BETWEEN ('2008-08-26 00:00:00') AND ('2008-08-28 23:59:59')
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]
September 5, 2008 at 12:25 am
one other way is you can also specify the case statement including IsDat check.
CASE WHEN IsDate(value) then CAST/Convert( value ) else "you can specify the default date " END
September 5, 2008 at 6:40 am
I think the problem is the order of when things take place. If you ask SQL Server to CAST something as part of the query in which you're checking it's date worthiness, you're not past that check at the time of the failure, so try the following:
declare @d1 as datetime, @d2 as datetime
set @d1 = cast ('2008-08-26 00:00:00' as datetime)
set @d2 = cast ( '2008-08-28 23:59:59' as datetime)
select value
into #table_2
from table
where name = 'DateTime'
and IsDate (value) = 1
select *
from #table_2
where cast(value as datetime) between @d1 and @d2
This way, you only cast those range values once, and you only attempt to cast the value field AFTER it's been validated for being dateworthy...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 5, 2008 at 10:01 am
If you stored date time in the '2008-08-26 00:00:00' format in your varchar(64) column instead of 8/29/2008 10:28:39 AM you could then just do string comparisons instead of converting them to datetime. This would also allow the use of an index.
September 5, 2008 at 10:31 am
Am I missing something?
when I run this I get not errors:
CREATE TABLE #test
(
Name char(10),
Value varchar(60)
)
INSERT INTO #test VALUES('DateTime', '8/29/2008 10:28:39 AM')
INSERT INTO #test VALUES('DateTime', '8/27/2008 11:40:39 AM')
INSERT INTO #test VALUES('DateTime', '8/26/2008 01:45:57 PM')
--select * from #test
select value from #test
where name = 'DateTime'
and cast ( value as datetime ) between cast ('2008-08-26 00:00:00' as datetime )
and cast ( '2008-08-28 23:59:59' as datetime )
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 5, 2008 at 10:35 am
OK looks like it's falling like everyone else said cause one or more of your fields don't include date values.
I'm def gonna go with the fact that the execution plan is running you cast/convert before the filter.
Hense trying to convert a non-date value to a datetime value.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 5, 2008 at 10:37 am
Yes, actually. The OP wanted to exclude any non-date values by using a check for being a date, but was trying to do that at the same time is CAST ing the value to datetime. I suggested a method that just dumps those records with valid dates into a temp table and then selects from the temp table those that meet the date criteria, having turned the two dates for the range into variables.
Steve
(aka smunson)
:):):)
Christopher Stobbs (9/5/2008)
Am I missing something?when I run this I get not errors:
CREATE TABLE #test
(
Name char(10),
Value varchar(60)
)
INSERT INTO #test VALUES('DateTime', '8/29/2008 10:28:39 AM')
INSERT INTO #test VALUES('DateTime', '8/27/2008 11:40:39 AM')
INSERT INTO #test VALUES('DateTime', '8/26/2008 01:45:57 PM')
--select * from #test
select value from #test
where name = 'DateTime'
and cast ( value as datetime ) between cast ('2008-08-26 00:00:00' as datetime )
and cast ( '2008-08-28 23:59:59' as datetime )
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 5, 2008 at 10:53 am
Do not cast the string values in the between clause as datetime. SQL Server already assumes the string is datetime. So when you cast those as datetime, it throws errors.
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply