March 28, 2012 at 12:31 am
Hi i created a procedure for inserting data into table when i passing values as parameter for date column it is showing error.can u please help any one.
CREATE PROCEDURE [dbo].[uspInsertFlight]
(
@flp_name [varchar](64) ,
@flp_campaign_id [int] ,
@flp_status_id [smallint] ,
@flp_target_aud_id [int] ,
@flp_budget [decimal](12, 2) ,
@flp_start_date [date] ,
@flp_end_date [date] ,
@flp_buyer_id [int] ,
@flp_pscope_fee [bit] ,
@flp_project_fee [bit],
@flp_advert_tax [bit] ,
@flp_objective [varchar](256) ,
@flp_ATB_date [date] ,
@flp_ATB_signed_by [varchar](64),
@flp_adv_contact_id [int] ,
@flp_age_contact_id [int] ,
@flp_po_number [varchar](64) ,
@flp_est_dds_code [varchar](32) ,
@flp_created_by_id [int] ,
@flp_created_date [date] ,
@flp_modified_date [date]
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @flp_id int;
INSERT INTO dbo.flight_period
([flp_name]
,[flp_campaign_id]
,[flp_status_id]
,[flp_target_aud_id]
,[flp_budget]
,[flp_start_date]
,[flp_end_date]
,[flp_buyer_id]
,[flp_pscope_fee]
,[flp_project_fee]
,[flp_advert_tax]
,[flp_objective]
,[flp_ATB_date]
,[flp_ATB_signed_by]
,[flp_adv_contact_id]
,[flp_age_contact_id]
,[flp_po_number]
,[flp_est_dds_code]
,[flp_created_by_id]
,[flp_created_date]
,[flp_modified_date])
VALUES
(@flp_name ,
@flp_campaign_id ,
@flp_status_id ,
@flp_target_aud_id ,
@flp_budget,
@flp_start_date ,
@flp_end_date ,
@flp_buyer_id ,
@flp_pscope_fee ,
@flp_project_fee ,
@flp_advert_tax ,
@flp_objective ,
@flp_ATB_date ,
@flp_ATB_signed_by ,
@flp_adv_contact_id ,
@flp_age_contact_id,
@flp_po_number ,
@flp_est_dds_code ,
@flp_created_by_id ,
@flp_created_date ,
@flp_modified_date )
SET @flp_id=SCOPE_IDENTITY();
select @flp_id as flp_id
SET NOCOUNT OFF;
END
DECLARE @return_value int
EXEC @return_value = [dbo].[uspInsertFlight]
@flp_name = N'Fest',
@flp_campaign_id = 8,
@flp_status_id = 2,
@flp_target_aud_id = 6,
@flp_budget = 5000,
@flp_start_date = 02-02-2012,
@flp_end_date = 02-05-2012,
@flp_buyer_id = 2,
@flp_pscope_fee = 0,
@flp_project_fee = 0,
@flp_advert_tax = 0,
@flp_objective = N'null',
@flp_ATB_date = 08-01-2012,
@flp_ATB_signed_by = N'null',
@flp_adv_contact_id = 2,
@flp_age_contact_id = 1,
@flp_po_number = N'1500',
@flp_est_dds_code = N'58',
@flp_created_by_id = 1,
@flp_created_date = 02-03-2012,
@flp_modified_date = null
SELECT 'Return Value' = @return_value
GO
error is Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '-'.
March 28, 2012 at 1:55 am
Put single quotes around the dates, while assigning to the parameters and hopefully it will work e.g.
@flp_start_date = '02-02-2012',
@flp_end_date = '02-05-2012',
March 28, 2012 at 2:30 am
Hi,
as u said single quotes i pass date within the quotes it is showing this msg.
Msg 8114, Level 16, State 5, Procedure uspInsertFlight, Line 0
Error converting data type varchar to date.
But the values are entering by front end they cant pass with quotes they will pass just date it has to insert into table.
March 28, 2012 at 2:54 am
For that you should send standard format for the dates i.e. yyyymmdd hh:mm:ss.mmm, yyyy-mm-ddThh:mm:ss.mmm OR yyyymmdd (since you do not need time part to be stored). As far as front end is concerned, I guess yyyy-mm-ddThh:mm:ss.mmm is used often, but again it depends upon the environment. Moreover, you should visit this BOL link to know about the SET LANGUAGE and SET DATEFORMAT
March 28, 2012 at 9:44 am
nhimabindhu (3/28/2012)
Hi i created a procedure for inserting data into table when i passing values as parameter for date column it is showing error.can u please help any one.CREATE PROCEDURE [dbo].[uspInsertFlight]
(
@flp_name [varchar](64) ,
@flp_campaign_id [int] ,
@flp_status_id [smallint] ,
@flp_target_aud_id [int] ,
@flp_budget [decimal](12, 2) ,
@flp_start_date [date] ,
@flp_end_date [date] ,
@flp_buyer_id [int] ,
@flp_pscope_fee [bit] ,
@flp_project_fee [bit],
@flp_advert_tax [bit] ,
@flp_objective [varchar](256) ,
@flp_ATB_date [date] ,
@flp_ATB_signed_by [varchar](64),
@flp_adv_contact_id [int] ,
@flp_age_contact_id [int] ,
@flp_po_number [varchar](64) ,
@flp_est_dds_code [varchar](32) ,
@flp_created_by_id [int] ,
@flp_created_date [date] ,
@flp_modified_date [date]
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @flp_id int;
INSERT INTO dbo.flight_period
([flp_name]
,[flp_campaign_id]
,[flp_status_id]
,[flp_target_aud_id]
,[flp_budget]
,[flp_start_date]
,[flp_end_date]
,[flp_buyer_id]
,[flp_pscope_fee]
,[flp_project_fee]
,[flp_advert_tax]
,[flp_objective]
,[flp_ATB_date]
,[flp_ATB_signed_by]
,[flp_adv_contact_id]
,[flp_age_contact_id]
,[flp_po_number]
,[flp_est_dds_code]
,[flp_created_by_id]
,[flp_created_date]
,[flp_modified_date])
VALUES
(@flp_name ,
@flp_campaign_id ,
@flp_status_id ,
@flp_target_aud_id ,
@flp_budget,
@flp_start_date ,
@flp_end_date ,
@flp_buyer_id ,
@flp_pscope_fee ,
@flp_project_fee ,
@flp_advert_tax ,
@flp_objective ,
@flp_ATB_date ,
@flp_ATB_signed_by ,
@flp_adv_contact_id ,
@flp_age_contact_id,
@flp_po_number ,
@flp_est_dds_code ,
@flp_created_by_id ,
@flp_created_date ,
@flp_modified_date )
SET @flp_id=SCOPE_IDENTITY();
select @flp_id as flp_id
SET NOCOUNT OFF;
END
DECLARE @return_value int
EXEC @return_value = [dbo].[uspInsertFlight]
@flp_name = N'Fest',
@flp_campaign_id = 8,
@flp_status_id = 2,
@flp_target_aud_id = 6,
@flp_budget = 5000,
@flp_start_date = 02-02-2012,
@flp_end_date = 02-05-2012,
@flp_buyer_id = 2,
@flp_pscope_fee = 0,
@flp_project_fee = 0,
@flp_advert_tax = 0,
@flp_objective = N'null',
@flp_ATB_date = 08-01-2012,
@flp_ATB_signed_by = N'null',
@flp_adv_contact_id = 2,
@flp_age_contact_id = 1,
@flp_po_number = N'1500',
@flp_est_dds_code = N'58',
@flp_created_by_id = 1,
@flp_created_date = 02-03-2012,
@flp_modified_date = null
SELECT 'Return Value' = @return_value
GO
error is Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '-'.
I notice you are using the Scope_Identity() Function.
You might want to note that this Function could on occasion return incorrect values.
You can see the following Microsoft Support Article for more details and workarounds.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply