how to catch this error

[bebblebrox]

Hi,

Any advice on the problem below? I have a 4 digit string representing a date which I can sucessfully convert when the year is prior to 2000. But, after 2000, I get a syntax error.

select convert(datetime,(substring(@date,1,2) + '01' + substring(@date,3,2)),101100) 

1999-01-08 00:00:00.000


set @date = 0508
select convert(datetime,(substring(@date,1,2) + '01' + substring(@date,3,2)),101100) 

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

 

[DrSql]

after your set statement add a print statement
Print @data
and see where you getting the error. It has to be a inalid date format.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[SQLBill]

That might be an issue with the two digit year...

Open Enterprise Manager, expand to the instance, right click on the instance, select Properties, then go to the Server Settings tab. Look at the Two Digit Year Support settings.

You might have to change that.

Also, in your example you show the value (@date) that you used for the second 'run', but what did you use for the first run?

Lastly, if @date is VARCHAR or CHAR, the 0508 should have single quotes around it: SET @date = '0508'

-SQLBill

Posting advice: FAQ481-4875

 

[RobertT687]

You need to enclose the variable in single quotes:
Set @Date = '0508' NOT Set @Date = 0508.

You are losing the leading zero.
This works.
declare @date char(4)
set @date = '0508'
select @date
select convert(datetime,(substring(@date,1,2) + '01' + substring(@date,3,2)),101100)

 

[bebblebrox]

Robert:

Yes, that works. But where I actually have this problem is in the following script:

--create cursor of existing records
Declare @origdate varchar(5), @id int

DECLARE cOrigdate CURSOR fast_forward for
SELECT origdate,id
FROM MEMBERS2 where len(origdate) = 4
ORDER BY id

begin
open cOrigdate
fetch next from cOrigdate
while @@FETCH_STATUS = 0 Begin
	fetch next from cOrigdate into @origdate, @id
	
		--convert origdate to datetime
	  	set @origdate = convert(datetime,(substring(@origdate,1,2) + '01' + substring(@origdate,3,2)),101100)
	  	if @@error<> 0
	  	begin
	  		print 'error: ' + @origdate
	  		break
	  	end
        print @origdate
	  	update members2 set new_origdate = @origdate where id = @id


end


end

close cOrigdate
deallocate cOrigdate

so in this case i don't have the option of using quotes.

futher to the point, I'd rather not do this in a script but with a bulk update.

 

[amrita418]

You are right, you do not need a cursor if all you are trying to do is update the date.

Can you post sample input / output expected for the @date and @origdate.

Regards,
AA

 

[amrita418]

Here is something that can get you started:

update  members2
set     new_origdate = convert(datetime,case when left(origdate, 2) = '19' then origdate + '0101' 
        else  left(origdate, 2) + '01' + right(origdate, 2) end)
where   len(origdate) = 4

PS: If origdate = 1999 then 1999-01-01 00:00:00.000 and if origdate = 0508 then 2005-01-08 00:00:00.000 is loaded.

Regards,
AA