How do you get the AM out of a date that doesn’t follow a pattern?

Asked

Viewed 38 times

0

My date comes in this format: 3/1/2020 12:00:00 AM (Realize it’s not 01 and 03, it’s only 1 and 3) But obviously it can also come with two numbers on day and month. Ex: 10/10/2020 This makes it more difficult to think about cutting the string, and I believe that would be a lot of gambit. I looked for how to transform Datetime into Date and saw that they cut, what would be harder for me with this problem of 1 or 2 numbers.

I wanted to know how to resent it out, or at least deal with it like I dealt with time, just left the time there and put the "HH24:MI:SS".

My part of the query is like this:

...AND  dat_inspecao_programada >= TO_DATE('3/1/2020 12:00:00 AM', 'MM/DD/YYYY HH24:MI:SS')...
sql oracle

  • It’s not hard to cut the string no, just split it by /

  • In Oracle that?

  • Yes, in Oracle. I came to a solution, converted to string by cutting the end using c#. But I have another problem: It should work, but it’s not working. What happened? Now it looks like this: ... AND dat_inspecao_programmed >= TO_DATE('01 March 2020', 'DD MM YYYY'); Error appears: "ORA-01841: year (full) must be between -4713 and +9999 and cannot be zero 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range"

1 answer

0

See if one of these solutions solves.

Convert with AM/PM:

TO_DATE('3/1/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS am', 'nls_date_language=american')

Convert with month in writing:

TO_DATE('01 March 2020', 'DD MONTH YYYY', 'nls_date_language=american')

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.