Converting char(10) column to Datetime

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

SQL Server Development (2000)

Converting char(10) column to Datetime

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2005-10-19 : 20:04:43

Hi,
I have a situation where I have to load table A from table B.
Table A has 8 columns. But I have only shown 3 for example.

Table A:
col1 numeric(18,0) --->Primary Key
,col2 char(1)
,receivedate datetime

Table B
col1 numeric(18,0),
col2 char(1),
scandate char(10) --- data in this column is like this: 02/04/2005.

When I try to insert data from table B to Table A....I am getting the message :

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.

I guess this is happening because scandate column is char(10) and I am trying to insert into receivedate column which datetime data type.

I tried CONVERT(datetime,RTRIM(LTRIM(ScanDate)),110) but receive error below:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Also I tried CAST(ScanDate AS DATETIME) but hit an error below:

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.

Please help how I can insert the data.

Thanks

Raj

Kristen
Test

22859 Posts

Posted - 2005-10-19 : 20:07:31

What format is the date in?

SQL Server will be assuming a format compatible with its Locale.

You can "force" the format using

SET DATEFORMAT DMY

before your INSERT or SELECT CONVERT(...

Just put the DMY letters in the order the date components are arrange in your char(10)

Kristen

aakash
Starting Member

8 Posts

Posted - 2005-10-19 : 20:14:53

Error 242 is a runtime error which occurs if the conversion from char(10) to datetime fails for a particular cell of data.

In your case, you probably have a date string in table B/column scandate which could not be correctly parsed, probably because it contained an invalid string representation of a date.

Note that datetime represents dates between Jan 1, 1753 and Dec 31, 9999. If your date is outside that range, you will need to maintain your own date representation.

-Aakash
Developer, SQL Server engine

rkumar28
Starting Member

49 Posts

Posted - 2005-10-19 : 23:21:04

Thanks a bunch for replying...It indeed turned out to be a data issue. One record had invalid string representation of a date.

Thanks for the timely help.

Raj

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-20 : 00:20:22

select

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-20 : 00:21:53

[code]select *
from TableB
where isdate(Scandate) = 0[/code]
...should find all the values that cannot be implicitly cast as dates.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 01:16:57

quote:
Originally posted by blindman

select *
from TableB
where isdate(Scandate) = 0

...should find all the values that cannot be implicitly cast as dates.

The Result purely depends on the Local Settings

Declare @t1 char(10)
Declare @t2 char(10)
set @t1='19/12/2005'
set @t2='12/19/2005'
Select IsDate(@t1) as 'dd/mm/yyyy',IsDate(@t2)as 'mm/dd/yyyy'

You can see the difference

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources