CODEWITHSUNDEEP
c#oracle-database
user147504
user147504

Reputation: 165

Oracle TO_DATE() function

I am trying to insert date into an oracle database which is imported from an excel spreadsheet. At some rows of spreadsheet, the date is represented in 24 hour format and at some rows it is represented in AM/PM format.

I started converting the date string to oracle format by using TO_DATE function as 

"TO_DATE(\'" + Timestamp + "\', 'MM/DD/YYYY HH:MI:SS AM', 'nls_date_language=american')"

The "Timestamp" here is a C# string variable which has date in string format.

I am getting exception when the timestamp variable is represented in 24 hour format. Is there any way that I can make my C# code accept both type of date formats and insert into oracle table as they are?

Upvotes: 0

Views: 1771

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26353

Here are two ways you can go:

C# (as suggested above)

DateTime.ParseExact has an overload that lets you pass multiple format strings. It will use the first one that works. DateTime.TryParseExact also has this overload. DateTime.Parse does not.

string[] formats = {"dd/MM/yyyy HH:mm:ss", "dd/MM/yyyy hh:mm:ss tt"};
DateTime Timestamp = DateTime.ParseExact(inputDate, formats, CultureInfo.InvariantCulture, DateTimeStyles.None);

Oracle

Have Oracle check for AM or PM and adjust the format string accordingly. This is the raw Oracle; I'll leave the string building to you:

TO_DATE(val,
  CASE WHEN REGEXP_LIKE(val, '(AM|PM)')
    THEN 'MM/DD/YYYY HH:MI:SS AM'
    ELSE 'MM/DD/YYYY HH24:MI:SS'
  END)

Any code issues let me know. I'm not at a machine where I could test these snippets.

Upvotes: 2

Related Questions