Store Procedure Conversion failed when converting datetime from character string

Dear Experts,

I'm trying to create a stored procedure to update a datetime field from 2 input parameters. (The reason it's like this is that I want to enter the start date/time simular to setting an Outlook reminder, where you have a Calendar Selector for the date and a Combo for the Time)

Below is the syntax:

ALTER PROCEDURE dbo.dsp_update_works
(
@work_id                              int,
@work_start_date      varchar,
@work_start_time      varchar
)
AS

UPDATE    dtb140_works
SET              work_start = CONVERT(nvarchar(50), @work_start_date, 106) + ' ' + CONVERT(nvarchar(50), @work_start_time, 108)
WHERE     (work_id = @work_id)

IF @@ERROR <> 0
BEGIN
RAISERROR ('Update Works failed.',18,1)
RETURN
END

When I run the sql it works ok. However, when I run it as a Store Procedure it errors with the following error:

Running [dbo].[dsp_update_works] ( @work_id = 2956, @work_start_date = 17/8/06, @work_start_time = 14:00:00 ).

Conversion failed when converting datetime from character string.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[dsp_update_works].

I've tried various alternatives in variables, but with no success, can someone please tell me what I'm doing wrong?

Many Thanks.


datavationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
U dont need the conversion, because already these are varchar
@work_start_date     varchar,
@work_start_time     varchar
0
Aneesh RetnakaranDatabase AdministratorCommented:
UPDATE    dtb140_works
SET              work_start =  @work_start_date + '  ' + @work_start_time
WHERE     (work_id = @work_id)

0
Aneesh RetnakaranDatabase AdministratorCommented:
declare @work_start_date     varchar(30),
@work_start_time     varchar (30)
declare @work_start datetime

select @work_start_date = '04-04-2006',@work_start_time ='11:30 pm'
SET @work_start =  @work_start_date + '  ' + @work_start_time
select @work_start

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

datavationAuthor Commented:
I've tried that and it still doesn't work.


Amending the SP to the below:

ALTER PROCEDURE dbo.dsp_update_works
(
@work_id                              int,
@work_start_time      varchar,
@work_start_date      varchar
)
AS

UPDATE    dtb140_works
SET              work_start =@work_start_date + ' ' + @work_start_time
WHERE     (work_id = @work_id)

IF @@ERROR <> 0
BEGIN
RAISERROR ('Update Works failed.',18,1)
RETURN
END

Gives the following Error:

Running [dbo].[dsp_update_works] ( @work_id = 2956, @work_start_time = 14:00, @work_start_date = 17/08/06 ).

Conversion failed when converting datetime from character string.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[dsp_update_works].
0
Anthony PerkinsCommented:
>>I've tried that and it still doesn't work.<<
Than you are not paying attention, as aneeshattingal has stated (no points for me please) you need to dimension the varchars, as in:

@work_start_time     varchar(30),
@work_start_date     varchar(30)
0
datavationAuthor Commented:
Sorry, I replied before getting your following 2 comments.

Further to your addtional comments, below is my amended SP. But it's still erroring?

ALTER PROCEDURE dbo.dsp_update_works
(
@work_id                  int,
@work_start_date    varchar(30),
@work_start_time    varchar (30)
)
AS

UPDATE    dtb140_works
SET              work_start =@work_start_date + ' ' + @work_start_time
WHERE     (work_id = @work_id)

IF @@ERROR <> 0
BEGIN
RAISERROR ('Update Works failed.',18,1)
RETURN
END
0
datavationAuthor Commented:
Thanks acperkins, but I was paying attention, I just hadn't recieved the additional comment, but as I say, it's still not working. (Correct, no points awarded ;-))
0
Aneesh RetnakaranDatabase AdministratorCommented:
need to use a SETDATEFORMAT DMY in ur sp
0
Aneesh RetnakaranDatabase AdministratorCommented:
SET DATEFORMAT DMY
SELECT @work_start_date = '17/08/06', @work_start_time = '14:00:00'

SET @work_start =  @work_start_date + ' ' + @work_start_time
select @work_start

0
Aneesh RetnakaranDatabase AdministratorCommented:
ALTER PROCEDURE dbo.dsp_update_works
(
@work_id                         int,
@work_start_time     varchar(15),
@work_start_date     varchar(15)
)
AS
SET DATEFORMAT DMY
UPDATE    dtb140_works
SET              work_start =@work_start_date + ' ' + @work_start_time
WHERE     (work_id = @work_id)

IF @@ERROR <> 0
BEGIN
RAISERROR ('Update Works failed.',18,1)
RETURN
END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
datavationAuthor Commented:
Many Thanks aneeshattingal, that's excellent.


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.