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.
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 thisCONVERT(datetime, Spend, 103)But this Error occuringThe 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 HelpThanks 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? |
|
|
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] |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
Kristen
Test
22859 Posts |
Posted - 2011-08-30 : 03:34:48
|
[code]SET DATEFORMAT DMYSELECT...where isdate(spend)=1 and len(Spend)=10[/code] |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-08-30 : 10:33:00
|
quote: Originally posted by Kristen
SET DATEFORMAT DMYSELECT...where isdate(spend)=1 and len(Spend)=10
MadhivananFailing to plan is Planning to fail |
|
|
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 1The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.Plz advice |
|
|
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. |
|
|
|
|
|
|
|