Error: Convert varchar datatype to datetime datat.

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

Error: Convert varchar datatype to datetime datat.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2013-06-28 : 06:27:20

Dear All,

I am trying following query. But I am getting conversion error.
Can anyone please help me how to resolve this issue ?

Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Query:
SELECT ProductId FROM [dbo].[Product_SpecificationAttribute_Mapping]
WHERE [SpecificationAttributeOptionId] IN (SELECT [Id] FROM [dbo].[SpecificationAttributeOption] WHERE ISDATE(Name)=1 and cast(Name as datetime) BETWEEN cast('10/06/2013' as datetime) AND cast('24/06/2013' as datetime) AND SpecificationAttributeId = 8)

Thanks,
Harish Patil

Thanks and Regard's
Harish Patil

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-28 : 06:38:48

That means there are values that could not be converted into datetime datatype. e.g.

declare @a varchar(100)
set @a='01201301'
select CONVERT(datetime, @a)

You'll need to find out the problematic data and fix it out. One way to find it is to use isDate function e.g.

declare @a varchar(100)
set @a='01201301'
select ISDATE(@a) -- 0 means invalid date value

set @a='20130101'
select ISDATE(@a) -- 1 means a valid date

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 06:40:35

i think issue is your language and dateformat settings.
always try to use unamibguos formats for passing date values

see

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

it should be


SELECT ProductId FROM [dbo].[Product_SpecificationAttribute_Mapping]
WHERE [SpecificationAttributeOptionId] IN (SELECT [Id] FROM [dbo].[SpecificationAttributeOption] WHERE ISDATE(Name)=1 
and Name >='20130610' 
AND Name < '20130625'
AND SpecificationAttributeId = 8)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 06:42:50

I hope Name is a column of type datetime. Also using BETWEEN will cause records in last day (20130624 in your case) after midnight to be ignored. Thats why i use >= and < interval limits to include entrie records within interval

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

hspatil31
Posting Yak Master

182 Posts

Posted - 2013-06-28 : 07:08:20

Hello Visakh,

Actually Name is column of navarchar(max).
This column contain the multiple value just like string, number or date.
So for that I am using ISDATE(Name)=1 in my query, so it will pick only dates.
I have tried your solution. It's givingwrong result. Means result should come with specific date range(from '10/06/2013' to '24/06/2013') its showing date of 2004 and 2011 also.

Can you please help how to resolve this issue ?

Query: SELECT ProductId FROM [dbo].Product_SpecificationAttribute_Mapping] WHERE [SpecificationAttributeOptionId] IN (SELECT [Id] FROM [dbo].[SpecificationAttributeOption] WHERE ISDATE(Name)=1 and Name >= '10/06/2013' AND Name <= '24/06/2013' AND SpecificationAttributeId = 8)

Thanks,
Harish Patil

Thanks and Regard's
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 07:19:22

Why are you using nvarchar field for storing dates? thats your biggest problem. Always use proper datatype for your fields

your current query will still throw error. i think what you need is this


SELECT ProductId FROM [dbo].Product_SpecificationAttribute_Mapping] WHERE [SpecificationAttributeOptionId] IN (SELECT [Id] FROM (SELECT CAST(Name AD datetime) AS Name,Id FROM [dbo].[SpecificationAttributeOption] WHERE ISDATE(Name)=1)t WHERE Name  >= '20130610' AND Name < '20130625' AND SpecificationAttributeId = 8)

using date values as 24/06/2013 etc will throw error if your server language settings is English united states
you should have read the article as it clearly explains this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources