| Author |
 Topic  |
|
|
asif372
Yak Posting Veteran
Pakistan
96 Posts |
 Posted - 08/15/2011 : 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
USA
355 Posts |
Posted - 08/15/2011 : 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)
Singapore
16769 Posts |
Posted - 08/15/2011 : 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
Time is always against us
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/30/2011 : 03:34:48
|
SET DATEFORMAT DMY
SELECT
...
where isdate(spend)=1 and len(Spend)=10
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 08/30/2011 : 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 |
|
|
|
dcs
Starting Member
6 Posts |
Posted - 09/15/2011 : 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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/15/2011 : 09:52:07
|
| There is some goofy data in that column, somewhere. Use the methods described above to locate which row(s) its in. |
|
|
| |
Topic |
|
Convert Varchar to datetime
