String was not recognized as a valid DateTime when using SQL Date

Question

I am using a date out of a SQL Server database that has the miliseconds on it like the example here "2016-10-07 21:00:29:987" which is held in a hidden field.

This is how the data is recorded, and this is one of the clauses of an update in a database.

When I try and pass this into the database as a parameter I get the "String was not recognized as a valid DateTime" error when using

var d = DateTime.ParseExact(hfDateLocked.Value.ToString(), "yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture);

If I remove the milliseconds off this works fine, but as its in the database with the milliseconds and this is one of the three clauses \ conditions I need to pass it through with this.

Any help would be gratefully received.

Edit 1

SqlParameter lockTimParam = new SqlParameter("@LockTime", SqlDbType.DateTime);

if (string.IsNullOrEmpty(hfLockTime.Value.ToString()))
{
    lockTimParam.Value = DBNull.Value;
}
else
{
    lockTimParam.Value = DateTime.ParseExact(hfLockTime.Value.ToString(), "yyyy-MM-dd hh:mm:ss:fff", CultureInfo.InvariantCulture);
}

Are you storing dates in the database as strings? Do not do that! — mason
– mason, Commented Oct 7, 2016 at 20:48
You have a DateTime object hfDateLocked and you're using DateTime.Parse ? This doesn't make any sense. If you want to format your DateTime object to a specific string format, you can just call .ToString and pass the desired format. Check the answer below. — Zein Makki
– Zein Makki, Commented Oct 7, 2016 at 20:50
@Mate using that it gives me the correct string, but kicks the milliseconds off — Simon Price
– Simon Price, Commented Oct 7, 2016 at 20:55
@mason no im not storing them in the db as a string, but when the value comes back im assigning it to a hiddent value, how else should I do it — Simon Price
– Simon Price, Commented Oct 7, 2016 at 20:59

Igor · Accepted Answer · 2016-10-07 21:12:54Z

1

Its a type-o in your format string, it does not match the format of the DateTime string that is being passed in (just the symbol between seconds and milliseconds).

.fff should be :fff.

var result = System.DateTime.ParseExact("2016-10-07 21:00:29:987", "yyyy-MM-dd HH:mm:ss:fff", System.Globalization.CultureInfo.InvariantCulture);

Or if the DateTime string is changed to use . instead of : then this works.

var result = System.DateTime.ParseExact("2016-10-07 21:00:29.987", "yyyy-MM-dd HH:mm:ss.fff", System.Globalization.CultureInfo.InvariantCulture);

Either way. The milliseconds in the returned DateTime string are segregated by a period (.) then use .fff in the format string, if its a colon (:) then use :fff in the format string.

edited Oct 7, 2016 at 21:12

answered Oct 7, 2016 at 21:05

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

String was not recognized as a valid DateTime when using SQL Date

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related