?
Solved

Error converting nvarchar to datetime

Posted on 2006-05-16
3
Medium Priority
?
668 Views
Last Modified: 2010-05-18
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???
0
Comment
Question by:sureshraina
  • 3
3 Comments
 
LVL 75

Expert Comment

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

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 16693183
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16731127
Thanks for Grade C
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

839 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