CODEWITHSUNDEEP
c#.net
NoviceToProgramming
NoviceToProgramming

Reputation: 103

Convert datetime from excel format into the sql format

I'm reading values from Excel and inserting read rows to SQL, but I'm facing an issue with date columns.

From Excel I'm reading the dates as 23-08-2011 01:33:01, but in the database the values are stored as 2011-08-22 10:21:18.000.

Is there a way I can convert in C# (not in T-SQL) the date I'm reading to a yyyy-mm-dd 00:00:00:000 format? I want to convert 23-08-2011 01:33:01 to 2011-08-23 00:00:00:000.

Upvotes: 3

Views: 1599

Answers (3)

Nemanja Boric
Nemanja Boric

Reputation: 22177

  1. Parse Excel string into DateTime object using DateTime.ParseExact
  2. Format DateTime object into SQL format using String.Format and the appropriate format string.

See these two resources for guide how to create a DateTime format string:

Standard Date and Time Format Strings

Custom Date and Time Format Strings

DateTime excel = DateTime.ParseExact("23-08-2011 01:33:01", "dd-MM-yyyy hh:mm:ss", CultureInfo.InvariantCulture);
String sqlString = String.Format("{0:yyyy-MM-dd hh:mm:ss:ffff}", excel);

Upvotes: 3

Hiệp Lê
Hiệp Lê

Reputation: 31

string dateString = "23-08-2011 01:33:01";            
string format = "dd-MM-yyyy hh:mm:ss";            
DateTime date = DateTime.ParseExact(dateString, format, CultureInfo.InvariantCulture);
string again = date.ToString("yyyy-MM-dd 00:00:00:000");

Upvotes: 1

Dave Zych
Dave Zych

Reputation: 21887

Use the ParseExact method from DateTime

DateTime parsedDate = DateTime.ParseExact("23-08-2011 01:33:01", 
    "dd-MM-yyyy hh:mm:ss", CultureInfo.InvariantCulture);

Depending on how you're inserting your records, you should be able to use your DateTime object, otherwise you can store it as a string:

string sqlDate = parsedDate.ToString("yyyy-mm-dd hh:mm:ss:fff");

Upvotes: 1

Related Questions