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
 Convert Varchar to datetime

Author  Topic 

asif372
Posting Yak Master

100 Posts

Posted - 2011-08-15 : 01:59:41
Dear Sir,
I and Converting Varchar value to date time in sql like this

CONVERT(datetime, Spend, 103)

But this Error occuring
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Warning: Null value is eliminated by an aggregate or other SET operation.

Kindly Help
Thanks in Advance

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-15 : 02:25:13
There are a few things it could be. What is the size of the varchar value? ie varchar(10), varchar(30) etc. Also, does the Spend column have any values other than characters compatible with datetime?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-15 : 02:57:56
you probably have bad data in there. Style 103 required the date string is in format DD/MM/YYYY.

You can use isdate() to find those bad data.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-29 : 05:36:58
You may have some bad dates. Find them using

where isdate(spend)=1 and len(Spend)=10

http://beyondrelational.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-08-30 : 03:34:48
[code]
SET DATEFORMAT DMY

SELECT
...
where isdate(spend)=1 and len(Spend)=10
[/code]
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-30 : 10:33:00
quote:
Originally posted by Kristen


SET DATEFORMAT DMY

SELECT
...
where isdate(spend)=1 and len(Spend)=10





Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dcs
Starting Member

6 Posts

Posted - 2011-09-15 : 09:45:54
I have a table with more then 1000 rows. One of the columns, 'dt' has a varchar datatype but has date values in it. i want to convert it into datetime , for all rows. But its not working.

The query :- SELECT CONVERT(datetime, dt,101) FROM [TestDB].[dbo].[test_date]

gave me the error:-

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


Plz advice
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-15 : 09:52:07
There is some goofy data in that column, somewhere. Use the methods described above to locate which row(s) its in.
Go to Top of Page
   

- Advertisement -