I have timestamp of Oracle:
string timestamp = "23-JUN-14 09.39.04.000000000 AM";
I am not able to parse it into system date time object. I used:
CultureInfo provider = CultureInfo.InvariantCulture;
String format = "yy-MMM-dd hh:mm:ss:fffffff";
string timestamp = "10-DEC-07 10.32.47.797201123 AM";
{
var date = DateTime.ParseExact(timestamp, format, provider);
DateTime dateTime = DateTime.ParseExact(timestamp.ToString(), "dd-MMM-y HH:mm:ss", CultureInfo.InvariantCulture);
}
It is still passing error. It is working 7 f after m but not more than that. I used try Parse, try ParseExact – is there any way?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Once we started to use ODP.NET, we had to implement an extension like below:
public static T ConvertOracleValue<T>(this object value)
{
if (value != null)
{
Type typeOfValue = value.GetType();
if (typeOfValue.Namespace.Contains("Oracle.DataAccess"))
{
if (typeOfValue.Name.Equals("OracleTimeStamp"))
{
int tempInt = 0;
Oracle.DataAccess.Types.OracleTimeStamp ots = (Oracle.DataAccess.Types.OracleTimeStamp)value;
tempInt = Int32.TryParse(ots.Millisecond.ToString("000").Substring(0, 3), out tempInt) ? tempInt : 0;
DateTime ret = new DateTime(ots.Year, ots.Month, ots.Day, ots.Hour, ots.Minute, ots.Second, tempInt);
return ConvertHelper.ConvertValue<T>(ret);
}
if (typeOfValue.Name.Equals("OracleTimeStampLTZ"))
{
int tempInt = 0;
Oracle.DataAccess.Types.OracleTimeStampLTZ ots = (Oracle.DataAccess.Types.OracleTimeStampLTZ)value;
tempInt = Int32.TryParse(ots.Millisecond.ToString("000").Substring(0, 3), out tempInt) ? tempInt : 0;
DateTime ret = new DateTime(ots.Year, ots.Month, ots.Day, ots.Hour, ots.Minute, ots.Second, tempInt);
return ConvertHelper.ConvertValue<T>(ret);
}
if (typeOfValue.Name.Equals("OracleTimeStampTZ"))
{
int tempInt = 0;
Oracle.DataAccess.Types.OracleTimeStampTZ ots = (Oracle.DataAccess.Types.OracleTimeStampTZ)value;
tempInt = Int32.TryParse(ots.Millisecond.ToString("000").Substring(0, 3), out tempInt) ? tempInt : 0;
DateTime ret = new DateTime(ots.Year, ots.Month, ots.Day, ots.Hour, ots.Minute, ots.Second, tempInt);
return ConvertHelper.ConvertValue<T>(ret);
}
string temp = value.ToString();
return ConvertHelper.ConvertValue<T>(temp);
}
}
else
{
return default(T);
}
return ConvertHelper.ConvertValue<T>(value);
}
where ConvertHelper.ConvertValue is another extension:
public static class ConvertHelper
{
public static T ConvertValue<T>(object value)
{
Type typeOfT = typeof(T);
if (typeOfT.BaseType != null && typeOfT.BaseType.ToString() == "System.Enum")
{
return (T)Enum.Parse(typeOfT, Convert.ToString(value));
}
if ((value == null || value == Convert.DBNull) && (typeOfT.IsValueType))
{
return default(T);
}
if (value is IConvertible)
{
return (T)Convert.ChangeType(value, typeOfT, new CultureInfo("en-GB"));
}
return (T)Convert.ChangeType(value, typeOfT);
}
}
This worked like a charm in our test, integration and production environments.
Method 2
According to https://stackoverflow.com/a/23198962/328864, there is no way to skip parts of an exact pattern, so i guess you could do something like this:
CultureInfo provider = CultureInfo.InvariantCulture;
string timestamp = "10-DEC-07 10.32.47.797201123 AM";
String format = String.Format("yy-MMM-dd hh.mm.ss.fffffff{0} tt", timestamp.Substring(26,2));
DateTime date = DateTime.ParseExact(timestamp, format, provider);
Console.WriteLine(date);
Not very pretty though.
Method 3
.NET DateTime structure has a precision of tick – 100 nanoseconds – 0.0000001 of second – 7 decimal positions after the point.
Oracle TimeStamp has a precision of up to nanosecond – 0.000000001 – 9 decimal positions after the point.
That is why standard DateTime cannot store all possible oracle TimeStamps. And its parsing function simply fail on more precise string representations of TimeStamp.
So, what could be tried:
- Format your TimeStamps in query to some format parseable by DataTime(with loss of precision if necessary) – http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm
- Or create your own more precise
CustomTimeStamp.Net structure and parse to it manually
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0