SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert Varchar to datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 08/15/2011 :  01:59:41  Show Profile  Reply with Quote
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

380 Posts

Posted - 08/15/2011 :  02:25:13  Show Profile  Reply with Quote
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)

Singapore
17655 Posts

Posted - 08/15/2011 :  02:57:56  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 08/29/2011 :  05:36:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 08/30/2011 :  03:34:48  Show Profile  Reply with Quote

SET DATEFORMAT DMY

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

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 08/30/2011 :  10:33:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 09/15/2011 :  09:45:54  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/15/2011 :  09:52:07  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000