I want to save date from excel to SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i m tring to save excel file data into sql table...excel data also ontains date
getting error: 'conversion fail date/or time from character string'

What I have tried:

my excel date format is 10/11/17 or 10/11/2017
in sql column type is datetime

my sp snippet -

SQL

MERGE INTO EmployeeDetails AS ED        
 USING @tvp_EmployeeDetails AS tED        
 ON ED.EmpCode = tED.EmpCode   
 AND isnull(ED.EmpCode,'') <> ''  
 and ED.EmpCode in (select EmpCode from dbo.UserDetails)  
 WHEN MATCHED THEN        
 UPDATE SET         
 ED.EmpCode = tED.EmpCode,        
 ED.DOJ = tED.DOJ,         
 ED.PermantAddress = tED.PermantAddress,        
 ED.PrimaryMissionID = tED.PrimaryMissionID,        
 ED.SecondaryMissionID = tED.SecondaryMissionID,        
 ED.AltitudeId = tED.AltitudeId,        
 ED.Extension = tED.Extension,        
 ED.ApplicationId_V1 = tED.ApplicationId_V1,        
 ED.ApplicationId_V2 = tED.ApplicationId_V2, 
 ED.ApplicationId_Others = tED.ApplicationId_Others, 
 ED.TrainingStartDate = tED.TrainingStartDate,  
 ED.TrainingEndDate = tED.TrainingEndDate,   
 ED.CertificationDate = tED.CertificationDate,   
 ED.CertificationAttempt = tED.CertificationAttempt,   
 ED.OnBoardDate = tED.OnBoardDate,
 ED.SeparationDate = case tED.SeparationDate when '' then null else  tED.SeparationDate end,
 ED.SeparationReason = tED.SeparationReason,
 ED.DisciplinaryAction = tED.DisciplinaryAction, 
 ED.EmpImage = tED.EmpImage,   
 ED.UpdatedBy =@UserID,     
 ED.UpdatedOn = GETDATE()      
 WHEN NOT MATCHED THEN      
INSERT ([EmpCode],[DOJ] ,[PermantAddress] ,[PrimaryMissionID] ,[SecondaryMissionID] ,[AltitudeId]  ,[Extension],[ApplicationId_V1]
       ,[ApplicationId_V2]  ,[ApplicationId_Others] ,[TrainingStartDate],[TrainingEndDate],[CertificationDate] ,[CertificationAttempt]
       ,[OnBoardDate] ,[SeparationDate],[SeparationReason],[DisciplinaryAction],[EmpImage] ,[CreatedBy],[CreatedOn])
 values (tED.EmpCode, case when isdate(tED.DOJ)=1 then tED.DOJ else null end ,
		tED.PermantAddress,tED.PrimaryMissionID,tED.SecondaryMissionID,tED.AltitudeId,tED.Extension,tED.ApplicationId_V1,
		tED.ApplicationId_V2, tED.ApplicationId_Others,	 case when isdate(tED.TrainingStartDate)=1 then tED.TrainingStartDate else null end,
		case when isdate(tED.TrainingEndDate)=1 then tED.TrainingEndDate else null end, case when isdate(tED.CertificationDate)=1 then tED.CertificationDate else null end,tED.CertificationAttempt,
		case when isdate(tED.OnBoardDate)=1 then tED.OnBoardDate else null end,
		case when isdate(tED.SeparationDate)=1 then tED.SeparationDate else null end ,tED.SeparationReason,tED.DisciplinaryAction,tED.EmpImage, @UserID, GETDATE()) ;

Posted 7-Feb-17 19:01pm

Member 12988411

Updated 7-Feb-17 20:30pm

Maciej Los

v2

Add a Solution

Comments

Suvendu Shekhar Giri 8-Feb-17 1:38am

Try to convert content of that column to "yyyy-MM-dd" and see if that helps :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2017-02-07T20:30:00

You have to convert that string date to a valid datetime before inserting to sql, e.g.

using System;
using System.Globalization;

public class Program
{
	public static void Main()
	{
		string dateString = "10/12/2017";
		string format1 = "d/M/yy";
		string format2 = "d/M/yyyy";
		DateTime dateTime;
		if (DateTime.TryParseExact(dateString, format1, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
		{
			Console.WriteLine(dateTime);
		}
		else if (DateTime.TryParseExact(dateString, format2, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
		{
			Console.WriteLine(dateTime);
		}
		else // not valid
		{
			Console.WriteLine("Not a valid date time, do something!");
		}
	}
}

Reference:
1. DateTime.TryParseExact Method (System)[^]
2. Custom Date and Time Format Strings[^]

Sahan2u · Answer 2 · 2017-02-07T20:36:00

First of all see what is the date format(MM-DD-YY or YYYY-MM-DD or YYYY/MM/DD....etc) of your SQL server use. This is based on the sql collation.

Let's say your sql server stored date & time in this format YYYY-MM-DD HH:MI:SS

Most simple thing you can do is

1. Copy all the data from your excel into a Note Pad. It will remove all the formats of excel define for your data.

2. Open a new excel file and press CTRL + A (select all).

3. Right click and select Format Cells

4. In the Format Cells window select Text as format

5. Go to Note Pad where you copy the data and press CTRL + A

6. Now copy and paste those data from Note Pad to Excel.

7. Now you have all the columns in text format. So edit the date (if needed) into the date format which your sql prefer. In my case format is YYYY-MM-DD

(Note: Don't worry about time part sql will add that itself)

8. Now try to import data from newly created excel sheet to your sql

I want to save date from excel to SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0