Author |
Topic |
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-02 : 12:18:15
|
Hello, I ama trying to Convert char to datetime and getting Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated.Here is my queryupdate test set date_time = CONVERT(DATETIME,(YEAR05)+ (MONTH05) + (DAY05), 130) where date_time > ''Table Descriptiondate_time datetime(8)year05 char(4)month05 char(2)day05 char(2)Thanks for any help.Rick |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-02 : 12:26:27
|
[code]select getdate() as date_time, '2005' as year05, '01' as month05, '01' as day05into #tmp1update #tmp1 set date_time = year05 + '-' + month05 + '-' + day05select * from #tmp1drop table #tmp1[/code]You shouldn't need to convert it explicitly.. |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-02 : 12:35:21
|
RickD, Thanks for the reply. But i do get out-of-range datetime value. I thin this is a data error.I have to figure out to get em out |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-03 : 09:12:29
|
What are the values you passed to the variables?MadhivananFailing to plan is Planning to fail |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-03 : 09:16:00
|
Rick, post some sample data from your table.----------------------------------'KH' |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-03 : 09:52:30
|
Sorry guys. I haven't been able to get back to you. Here is the sample data.year05 month05 date_time day051976 01 NULL 011976 01 NULL 011974 01 NULL 011974 01 NULL 011972 01 NULL 011976 01 NULL 011973 01 NULL 011985 01 NULL 011971 01 NULL 011971 01 NULL 011992 01 NULL 011991 01 NULL 011930 01 NULL 011987 01 NULL 011976 01 NULL 011990 01 NULL 011991 01 NULL 011991 01 NULL 011991 01 NULL 011992 01 NULL 011992 01 NULL 011992 01 NULL 011993 01 NULL 011993 01 NULL 011993 01 NULL 011994 01 NULL 011994 01 NULL 011994 01 NULL 011995 01 NULL 011990 01 NULL 011995 01 NULL 011995 01 NULL 011996 01 NULL 011996 01 NULL 011997 01 NULL 011997 01 NULL 011998 01 NULL 011998 01 NULL 011999 01 NULL 011999 01 NULL 011999 01 NULL 012000 01 NULL 012000 01 NULL 012000 01 NULL 012001 01 NULL 012001 01 NULL 01 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-03 : 10:02:33
|
[code]declare @t table(years char(4), months char(2), dates datetime,days char(2))insert into @tselect '1976', '01', NULL, '01' union allselect '1976', '01', NULL, '01' union allselect '1974', '01', NULL, '01'select * from @tupdate @t set dates=years+months+daysselect * from @t[/code]MadhivananFailing to plan is Planning to fail |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-02-03 : 10:04:49
|
quote: CONVERT(DATETIME,(YEAR05)+ (MONTH05) + (DAY05), 130)
Shouldn't that be 112, not 130? I don't imagine you really want to convert from strings representing Hijri dates about 560 years in the future? Even so, it shouldn't fail for the test data you give.Though obviously, if they aren't Hijri dates and you have 31st of any month (and 30th of some months), you will get type conversion errors since Islamic months have 29 or 30 days. |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-03 : 10:09:04
|
Thanks Madhivanan. Going back to where i started. The update query was just working fine with my SQL Server 6.5 database. I upgraded 6.5 to 2000 on same machine and since then i am having this problem. Thank again for your help. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-03 : 10:12:38
|
If you try the following, does it error and which one does it error on:select year(year05) from testselect month(month05) from testselect day(day05) from test If it doesn't fall over on any, you probably have a 29th Feb or something similar in there somewhere.. |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-03 : 10:17:04
|
Rick, error on both month05 and day05 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-03 : 10:20:42
|
Ok, try this:select month05 from test where convert(int,month05) > 12select day05 from test where convert(int,day05) > 31 If you get results, those are your problem values..Also, if this errors, you've probably got alpha characters in there.. |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-03 : 10:24:02
|
No rows returned for both queries |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-03 : 10:29:42
|
But they didn't fail?hmmm.. How about < 1 on both queries instead? |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-02-03 : 10:34:47
|
quote: Originally posted by ricky_newbee Rick, error on both month05 and day05
That's hardly surprising, is it? You can't convert a string of two digits to a datetime value, so MONTH('01') or DAY('01') will always fail!I've already told you, change the <deleted> 130 to 112! |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-03 : 10:36:59
|
Ok. I think there are rows with empty SPACES.Here is what i triedselect count(day05) from test where day05 is null0 rowsselect count(day05) from test where day05 = ' '(10243 row() affected)So, that the same number of rows when i run < 1 on day05 |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-03 : 10:41:33
|
quote: Originally posted by Arnold Fribble
quote: Originally posted by ricky_newbee Rick, error on both month05 and day05
That's hardly surprising, is it? You can't convert a string of two digits to a datetime value, so MONTH('01') or DAY('01') will always fail!I've already told you, change the <deleted> 130 to 112!
Arnold, changing 130 to 112 took care of convertion. But did not update rows. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-03 : 10:59:07
|
quote: Originally posted by ricky_newbee Ok. I think there are rows with empty SPACES.Here is what i triedselect count(day05) from test where day05 is null0 rowsselect count(day05) from test where day05 = ' '(10243 row() affected)So, that the same number of rows when i run < 1 on day05
You are going to need to update these to get rid of the spaces first then..update test set day05 = LTRIM(RTRIM(day05)) |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-03 : 11:53:29
|
Sorry Rick. Was woking with other stuff. And yes. I updated em and still have no luck. I think there is lot of data problems in the table. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-03 : 11:56:16
|
Yeah, sounds like it.. Sort the data out and i'm sure it'll work..When the following works, your problems are solved.. select year(year05) from testselect month(convert(int,month05)) from testselect day(convert(int,day05)) from test |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2006-02-03 : 12:13:04
|
Thanks RickD i appreciate your help.Thanks every one |
|
|
Next Page
|