Error converting nvarchar to datetime

Hello,

In my SP I dont have any column with type nvarchar even then I am getting this error when executed from ASPX page. Here is my stored procedure


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[db_Insert_Location]
         @address_1 varchar(50),
         @address_2 varchar(50),
           @address_3 varchar(50),
          @city varchar(50) ,          
         @State varchar(20),
         @zip   varchar(20),
         @country varchar(30),
         @location_code char(7),
           @description varchar(50),
         @rate_royalty  float,
         @rate_marketing float,
         @rate_other     float ,
         @currency_name varchar(50),
         @timezone  varchar(10),
         @region_name varchar(20),
         @peer_group_name varchar(20),
         @master_franchise bit ,
         @date_opened datetime ,
         @owner_name_first varchar(50),
         @owner_name_last varchar(50),
         @location_monetary_value money,
         @employee_count int
           
AS
BEGIN

    Declare @zaddress_id int
     Declare @zlocation_id int
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;

    -- Insert Address details first

   Insert into db_address(address_1,address_2,address_3,city,state,zip,country)
   values(@address_1,@address_2,@address_3,@city,@state,@zip,@country)

   set @zaddress_id = (select scope_identity())

   Insert into db_location_master(location_code,description,zaddress_id,rate_royalty,rate_marketing,rate_other,currency_name,timezone,region_name,peer_group_name,master_franchise)
   values(@location_code,@description,@zaddress_id,@rate_royalty,@rate_marketing,@rate_other,@currency_name,@timezone,@region_name,@peer_group_name,@master_franchise)    

   set @zlocation_id = (select scope_identity())

   Insert into db_location_ownership(zlocation_id,date_opened,owner_name_first,owner_name_last,location_monetary_value,employee_count)
   values(@zlocation_id,CAST(@date_opened as datetime),@owner_name_first,@owner_name_last,@location_monetary_value,@employee_count)  


END


what my be the problem???
sureshrainaAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Ypu can pass the value    @date_opened datetime  as '21-Apr-2006'

Otherwise in the format yyyy-mm-dd which is the default format in sql server

otherwise
you need to modify the SP change the datatype of date to varchar , and inside the sp use this statement immediately after the Begin

SET DATEFORMAT DMY -- if you are using ddmmyyyy for dates otherwise mdy for mmddyyyy
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Thanks for Grade C
0
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.

All Courses

From novice to tech pro — start learning today.