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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 CONVERT

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 1
The 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 query

update test set date_time = CONVERT(DATETIME,(YEAR05)+ (MONTH05) + (DAY05), 130) where date_time > ''

Table Description

date_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 day05
into #tmp1

update #tmp1 set date_time = year05 + '-' + month05 + '-' + day05

select * from #tmp1

drop table #tmp1
[/code]

You shouldn't need to convert it explicitly..
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 09:12:29
What are the values you passed to the variables?

Madhivanan

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-03 : 09:16:00
Rick, post some sample data from your table.

----------------------------------
'KH'


Go to Top of Page

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 day05
1976 01 NULL 01
1976 01 NULL 01
1974 01 NULL 01
1974 01 NULL 01
1972 01 NULL 01
1976 01 NULL 01
1973 01 NULL 01
1985 01 NULL 01
1971 01 NULL 01
1971 01 NULL 01
1992 01 NULL 01
1991 01 NULL 01
1930 01 NULL 01
1987 01 NULL 01
1976 01 NULL 01
1990 01 NULL 01
1991 01 NULL 01
1991 01 NULL 01
1991 01 NULL 01
1992 01 NULL 01
1992 01 NULL 01
1992 01 NULL 01
1993 01 NULL 01
1993 01 NULL 01
1993 01 NULL 01
1994 01 NULL 01
1994 01 NULL 01
1994 01 NULL 01
1995 01 NULL 01
1990 01 NULL 01
1995 01 NULL 01
1995 01 NULL 01
1996 01 NULL 01
1996 01 NULL 01
1997 01 NULL 01
1997 01 NULL 01
1998 01 NULL 01
1998 01 NULL 01
1999 01 NULL 01
1999 01 NULL 01
1999 01 NULL 01
2000 01 NULL 01
2000 01 NULL 01
2000 01 NULL 01
2001 01 NULL 01
2001 01 NULL 01
Go to Top of Page

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 @t
select '1976', '01', NULL, '01' union all
select '1976', '01', NULL, '01' union all
select '1974', '01', NULL, '01'
select * from @t
update @t set dates=years+months+days
select * from @t
[/code]

Madhivanan

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

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.
Go to Top of Page

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.
Go to Top of Page

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 test

select month(month05) from test

select day(day05) from test


If it doesn't fall over on any, you probably have a 29th Feb or something similar in there somewhere..
Go to Top of Page

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2006-02-03 : 10:17:04
Rick,
error on both month05 and day05
Go to Top of Page

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) > 12

select 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..
Go to Top of Page

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2006-02-03 : 10:24:02
No rows returned for both queries
Go to Top of Page

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?
Go to Top of Page

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!
Go to Top of Page

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 tried

select count(day05) from test where day05 is null
0 rows

select count(day05) from test where day05 = ' '
(10243 row() affected)

So, that the same number of rows when i run < 1 on day05

Go to Top of Page

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.

Go to Top of Page

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 tried

select count(day05) from test where day05 is null
0 rows

select 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))
Go to Top of Page

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.
Go to Top of Page

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 test

select month(convert(int,month05)) from test

select day(convert(int,day05)) from test

Go to Top of Page

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2006-02-03 : 12:13:04
Thanks RickD i appreciate your help.

Thanks every one
Go to Top of Page
    Next Page

- Advertisement -