Date Conversion Problem - SQL Server Forums

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)

Date Conversion Problem

Author

Topic

RegTyler
Starting Member

11 Posts

Posted - 2005-07-18 : 07:45:29

I am trying to convert dates stored in a nvarchar(10) field to Dates that will be stored in a datetime field

Whenever I run the Update query

UPDATE dbo.LMSALES
SET TDATE = CONVERT(nvarchar(10), TRANSDATE, 106)

I get an arithmetic overflow error. How can I find the rows that are generating this error or set those rows to NULL? Thanks

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-07-18 : 07:48:59

UPDATE dbo.LMSALES
SET TDATE = CONVERT(DATETIME, TRANSDATE)

OR

UPDATE dbo.LMSALES
SET TDATE = CAST(TRANSDATE AS DATETIME)

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-18 : 07:52:29

Post the sample data

Madhivanan

Failing to plan is Planning to fail

RegTyler
Starting Member

11 Posts

Posted - 2005-07-18 : 07:53:21

Sorry Transdate is the old nvarchar field and tdate is the new datetime field. If i make your chages I get the error "Syntax error converting datetime from character string" Thanks

RegTyler
Starting Member

11 Posts

Posted - 2005-07-18 : 07:55:05

quote:
Originally posted by RegTyler

Sorry Transdate is the old nvarchar field and tdate is the new datetime field. If i make your chages I get the error "Syntax error converting datetime from character string" Thanks

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-07-18 : 07:56:56

Then it probably is a data issue - post some sample data like Madhivanan
said - or check the data for some values that can possibly cause problems ie being out of date ranges.

Duane.

RegTyler
Starting Member

11 Posts

Posted - 2005-07-18 : 07:59:53

I also get the arithmetic overflow errormessage when I try to cast. I can't post the data, it is about 100k records that were imported into MSDE from an Access database.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-18 : 08:01:42

Post 10 data only

Madhivanan

Failing to plan is Planning to fail

RegTyler
Starting Member

11 Posts

Posted - 2005-07-18 : 08:34:40

TRANSDATE
11/28/1995
Here are some dates. What I'd like to do is convert the dates that can be converted and flag the dates that are bad.

11/28/1995
11/27/1995
11/27/1995
11/29/1995
11/30/1995
12/1/1995
12/1/1995
12/1/1995
12/1/1995
12/1/1995
12/1/1995
12/9/1995

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-18 : 08:48:23

use the IsDate() function to help you out.

- Jeff

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-18 : 08:51:41

I think no need for convertion

declare @t table (d nvarchar(12),d1 datetime null)
insert into @t(d) values('11/28/1995')
insert into @t(d) values('12/1/1995')
update @t set d1=d
select * from @t

Madhivanan

Failing to plan is Planning to fail

RegTyler
Starting Member

11 Posts

Posted - 2005-07-18 : 09:23:05

Thanks guys, the IsDate function helped me find the bad row, now the update / convert SQL statements work!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources