Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Store Procedure Conversion failed when converting datetime from character string

Posted on 2006-04-06
11
Medium Priority
?
10,483 Views
Last Modified: 2011-08-18
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.


0
Comment
Question by:datavation
  • 6
  • 4
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16393747
U dont need the conversion, because already these are varchar
@work_start_date     varchar,
@work_start_time     varchar
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16393761
UPDATE    dtb140_works
SET              work_start =  @work_start_date + '  ' + @work_start_time
WHERE     (work_id = @work_id)

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16393785
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:datavation
ID: 16393807
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16393872
>>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
 

Author Comment

by:datavation
ID: 16393888
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
 

Author Comment

by:datavation
ID: 16393917
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16393983
need to use a SETDATEFORMAT DMY in ur sp
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16393984
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16393990
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
 

Author Comment

by:datavation
ID: 16394127
Many Thanks aneeshattingal, that's excellent.


0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question