Hi There

I am trying to import data into my SQL server from a csv file. I imported the file into a staging table on the SQL server, and all fields are varchar (8000).

I am using the code below to try and get the data from the staging table, and will then Insert it into the relevant table, however I am getting the following error:

Server: Msg 242, Level 16, State 3, Line 66
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

DECLARE curTempClients CURSOR FOR
	SELECT 	"Full Name",
		"Address",
		"Phone Number (H)",
		"Phone Number (W)",
		"Notes",
		"Status",
		"Email Address",
		"Consultant",
		CAST("Related Company" AS INT),
		"Title",
		"Mobile Number",
		CAST("Date of Birth" AS DATETIME), 
		"Nationality",
		"Marital Status",
		"Name of Spouse/Partner",
		"Spouse/Partner Visa Status",
		"Any of you Previous Divorce",
		"Spouse/Partner Apply With",
		"Children Apply With",
		CAST("Applications" AS INT),
		"Referred By",
		CAST(CAST("Total Our Fee" AS MONEY) AS DECIMAL(12,2)),
		CAST("Related Enquiry" AS INT),
		CAST("Record ID#" AS INT)
	FROM 	TempClients
 
DECLARE @FullName varchar(100)
DECLARE @Address varchar(1000)
DECLARE @HomeNumber varchar(100)
DECLARE @WorkNumber varchar(100)
DECLARE @Notes varchar(2000)
DECLARE @Status varchar(100)
DECLARE @Email varchar(100)
DECLARE @Consultant varchar(100)
DECLARE @RelatedCompany int
DECLARE @Title varchar(100)
DECLARE @MobileNumber varchar(100)
DECLARE @DOB datetime
DECLARE @Nationality varchar(100)
DECLARE @MaritalStatus varchar(100)
DECLARE @SpouseName varchar(100)
DECLARE @SpouseVisa varchar(100)
DECLARE @Divorce varchar(100)
DECLARE @SpouseApplyWith varchar(100)
DECLARE @ChildrenApplyWIth varchar(100)
DECLARE @Applications int
DECLARE @ReferredBy varchar(100)
DECLARE @TotalOurFee decimal
DECLARE @RelatedEnquiry int
DECLARE @RecordId int
 
OPEN curTempClients
 
FETCH NEXT FROM curTempClients
INTO 	@FullName, @Address, @HomeNumber, @WorkNumber, @Notes, @Status, @Email, @Consultant, @RelatedCompany, @Title, @MobileNumber, 
	@DOB, @Nationality, @MaritalStatus, @SpouseName, @SpouseVisa, @Divorce, @SpouseApplyWith, @ChildrenApplyWIth, @Applications,
	@ReferredBy, @TotalOurFee, @RelatedEnquiry, @RecordId
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
	
	PRINT 'Full Name: ' + @FullName
	
	FETCH NEXT FROM curTempClients
	INTO 	@FullName, @Address, @HomeNumber, @WorkNumber, @Notes, @Status, @Email, @Consultant, @RelatedCompany, @Title, @MobileNumber, 
		@DOB, @Nationality, @MaritalStatus, @SpouseName, @SpouseVisa, @Divorce, @SpouseApplyWith, @ChildrenApplyWIth, @Applications,
		@ReferredBy, @TotalOurFee, @RelatedEnquiry, @RecordId
 
END
 
CLOSE curTempClients
 
DEALLOCATE curTempClients
 
GO

Select allOpen in new window