Syntax error converting datetime from character st

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)

Syntax error converting datetime from character st

Author

Topic

Oke_akoro
Starting Member

4 Posts

Posted - 2007-08-01 : 09:12:22

when I run the following update sql statement, it fails with error 'Syntax error converting datetime from character st'

update dbo.grants
set grcrtdate = convert(char,substring(str(grcrtd),1,6)+'-'+ substring(str(grcrtd),7,2)+'-'+substring(str(grcrtd),9,2),101) from dbo.grants
where grgrno in (select distinct grgrno from dbo.grants)

but if I run the following script it runs fine.

update dbo.grants
set grcrtdate = convert(char,substring(str(grcrtd),1,6)+'-'+ substring(str(grcrtd),7,2)+'-'+substring(str(grcrtd),9,2),101) from dbo.grants
where grgrno in ('000442/Z/80/Z','000177/Z/72/Z')

Please help I need to update about 100,000 rows.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-01 : 09:15:07

Can you post some sample data of grcrtd column?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 09:15:37

Sample data please.

E 12°55'05.25"
N 56°04'39.16"

Oke_akoro
Starting Member

4 Posts

Posted - 2007-08-01 : 12:09:20

sample data of grcrtd are: 19720501, 19801001, 19850301

Oke_akoro
Starting Member

4 Posts

Posted - 2007-08-01 : 12:13:59

These dates are stored as decimal datatype and needs to be converted to date

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-02 : 01:17:03

See if this works

Select cast(grcrtd as datetime) from table

Madhivanan

Failing to plan is Planning to fail

Oke_akoro
Starting Member

4 Posts

Posted - 2007-08-02 : 06:07:08

"Select cast(grcrtd as datetime) from dbo.grants" did not work

see error message: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 06:58:45

declare @a int
set @a = 20070802
Select cast(cast(@a as varchar) as datetime)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources