Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Devarsh.

Asked: August 06, 2010 - 5:21 am UTC

Last updated: February 02, 2018 - 12:13 am UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hi Tom,

In my pl/sql program I get date in format of string ..
say like '2010/06/30 00:00:00'

Now I need to convert this into a date and pass it as a parameter to another program..

what is the best way to that..

do i need to read nls_date_format from v$parameter ?

Thanks


and Tom said...

just pass

to_date( variable_that_contains_that_value, 'yyyy/mm/dd hh24:mi:ss' )


you already KNOW what the date format is - you have it in a string in a known format and need to convert it to a date.

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DateFormat

Anand, August 09, 2010 - 5:48 am UTC

Tom,
I would like to know is there any way that we can store a date value without AM/PM. I.e. if the time is AM then we should have the value as 8/9/2010 12:00:00 and if it is PM it should have the value as 8/9/2010 13:00:00. With the value I can differentiate it as AM and PM.

Plese let me know your suggestion.
Tom Kyte
August 09, 2010 - 1:53 pm UTC

insert

trunc(:dt,'d')+ case when to_char(:dt,'hh24') < '12' then 12 else 13 end/24


and put a check constraint on that column to check:

to_char(that_col,'hh24miss') in ('120000','130000')


If you have more than one place the insert happens - you should of course wrap the transaction in plsql so the developers are not burdened with remembering to do this - just have them call the stored procedure (that is, no triggers please)

RE: DateFormat

Duke Ganote, August 09, 2010 - 10:50 am UTC

Anand-- Your question seems inconsistent: you want to "store a date value without AM/PM", but your examples show AM and PM.

AM/PM are simply display formats of Oracle date/times:

select to_char
       ( trunc(sysdate)
       , 'yyyy-Mon-dd HH:MI:SS AM') AS "Midnight"
     , to_char
       ( trunc(sysdate) + interval '0 00:00:01' day to second
       , 'yyyy-Mon-dd HH:MI:SS AM') AS "Midnight + 1 sec"
     , to_char
       ( trunc(sysdate) - interval '0 00:00:01' day to second
       , 'yyyy-Mon-dd HH:MI:SS AM') AS "Midnight - 1 sec"
     , to_char
       ( trunc(sysdate) + interval '0 12:00:00' day to second
       , 'yyyy-Mon-dd HH:MI:SS AM') AS "Noon"
  from dual
/

Midnight                Midnight + 1 sec        Midnight - 1 sec        Noon
----------------------- ----------------------- ----------------------- -----------------------
2010-Aug-09 12:00:00 AM 2010-Aug-09 12:00:01 AM 2010-Aug-08 11:59:59 PM 2010-Aug-09 12:00:00 PM

Tom Kyte
August 09, 2010 - 1:58 pm UTC

he actually just wants to store am pm :)

he wants a value that represents 'morning' and another that represents 'not morning'

RE: AM/PM

Duke Ganote, August 09, 2010 - 6:54 pm UTC

It's the usage of noon and 1pm to represent AM and PM that seems odd. Both times are PM. Why not just midnight and noon? (Or even 6am and 6pm). Then the 'yyyy-Mon-dd AM' format works nicely:

select to_char
       ( trunc(sysdate)
       , 'yyyy-Mon-dd AM') AS "Midnight"
     , to_char
       ( trunc(sysdate) + interval '0 12:00:00' day to second
       , 'yyyy-Mon-dd AM') AS "Noon"
  from dual;

Midnight       Noon
-------------- --------------
2010-Aug-09 AM 2010-Aug-09 PM

Date Format

Anand, August 10, 2010 - 6:44 am UTC

Many thanks Tom/Duke for your valuable tips.

Confusing

Bill C, August 10, 2010 - 7:52 am UTC

Unless this is some type of homework assignment I cannot wrap my mind around about how storing a different date to indicate AM/PM would be useful since that information is already contained in the original date.

For Example
scott@DEAIS79> select to_char(sysdate, 'A.M.') from dual;

TO_C
----
P.M.


Tom Kyte
August 10, 2010 - 7:53 am UTC

It is not much different than someone that just wants to store a DATE without time is it.


I mean - "I cannot wrap my mind around
about how storing a different date to indicate JUST THE DAY would be useful since that information is already contained in the original date.
"

to_char( sysdate, 'dd-mon-yyyy' )

right? Same discussion - different level of detail.

Misleading

adderek, August 17, 2010 - 4:18 pm UTC

Hi,
The original question was not clear enough: is the "another program" a pl/sql code?
Tom's answer is simple and clear but not providing some information, that is: AFAIK Oracle date is format-less.
And you should be aware that there is something called "international date format" defined in ISO-8601.
Regards

Datatype Interval Formatting

Praveen Ray, November 28, 2010 - 4:22 am UTC

Hey Tom,

Thanks to your great responses. I have come across the following situation:

SQL> select to_char(TO_DSINTERVAL('00 ' || '05:14:10'), 'MI:SS') from dual;

TO_CHAR(TO_DSINTERVAL('00'||'
-----------------------------
+000000000 05:14:10.000000000

I expect, the output to be 14:10. Please, correct me.

Regards,
Ray.

Tom Kyte
November 28, 2010 - 3:20 pm UTC

to_char is defined for numbers, dates and strings - but not intervals. hence, there is an internal conversion of that interval to a number/date/string happening and the format just doesn't apply.

Agreed - it should probably throw an error, not silently "succeed".

I hate implicit conversions...

Datatype Interval Formatting

Praveen Ray, November 29, 2010 - 10:49 am UTC

Hi Tom, Good Evening!

Thank you so much for your reply. I am so happy to see a reply feed :)

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions200.htm#i1009324

In here, it's documented for Interval datatype too, that's why I got confused. I love Oracle so much, and I assume it to be perfect. And, I follow your work-around technique to get my every requirement done.

Regards,
Ray.
Tom Kyte
November 29, 2010 - 11:36 am UTC

there are no formats that actually map to the interval bits, they only work for dates.

You would use EXTRACT() to get the bits out you wanted.

Date_Function

Sahul, September 08, 2011 - 5:43 am UTC

Hi Tom,
I want to get date value range is 08/28/2011 12:00:00 AM.

I have one table contain numerious records. I want to pull the data in weekly basis, I have one date field which is Entry_date. Using this Entry_date I want to pull the report for previous week. ex, where Entry_date between 08/28/2011 12:00:00 AM and 09/04/2011 12:00:00 AM. Please tell me which function give me this exact date value. Since the date value is not manualually entered.
Tom Kyte
September 08, 2011 - 5:33 pm UTC

I want to get date value range is 08/28/2011 12:00:00 AM.


that is *not* a range, that is a very specific point in time.


I'm not sure what you are trying to ask with this:



where Entry_date between 08/28/2011
12:00:00 AM and 09/04/2011 12:00:00 AM. Please tell me which function give me
this exact date value. Since the date value is not manualually entered.



You wrote this on sep 8th, you want aug-28 midnight through sep-4 midnight (mon through sun) - so I guess you want to take the current date and find the previous sunday and then subtract a week and get all in between. If I guessed right, I not only win a prize but I can answer your question.


ops$tkyte%ORA11GR2> select
  2  next_day( trunc(sysdate,'dy')-7, 'sun' ) -6,
  3  next_day( trunc(sysdate,'dy')-7, 'sun' ) +1-1/24/60/60
  4  from dual
  5  /

NEXT_DAY(TRUNC(SYSDA NEXT_DAY(TRUNC(SYSDA
-------------------- --------------------
29-aug-2011 00:00:00 04-sep-2011 23:59:59



test that to make sure it gets the right sunday as far as you are concerned when executed on sunday!

you want all of the rows where the dates are between those two values I believe.


If you do not spell sunday SUN, or you want to be safe, you can use:

ops$tkyte%ORA11GR2> select
  2  next_day( trunc(sysdate,'dy')-7, to_char( to_date('01/07/1900','dd/mm/yyyy'), 'dy' ))-6,
  3  next_day( trunc(sysdate,'dy')-7, to_char( to_date('01/07/1900','dd/mm/yyyy'), 'dy' )) +1-1/24/60/60
  4  from dual
  5  /

NEXT_DAY(TRUNC(SYSDA NEXT_DAY(TRUNC(SYSDA
-------------------- --------------------
29-aug-2011 00:00:00 04-sep-2011 23:59:59


TRY_PARSE eqivivalent

Ravi B, July 12, 2013 - 7:01 pm UTC

Tom,

We get data from several clients which has variable date formats in one of the text column. We want to parse the text value and convert into date if possible. I could do TO_DATE and handle the exception but i dont know what the format mask would be in advance hence it is not practical to handle all the format masks with TO_DATE.

We have two systems, one on oracle and other on SQL server. In SQLServer we use combination of ISDATE and TRY_PARSE function which are format agnostic.

Do we have similar feature in oracle or is there a way we could construct one?

Thanks always!
Tom Kyte
July 16, 2013 - 4:09 pm UTC

what the heck do they do with

01/02/03


????? seriously - how does this work and give you valid data?

dateformat

Ravi B, July 16, 2013 - 8:45 pm UTC

That was my exact question to our folks. I have no idea what and how they(Microsoft) does :) My folks are happy as long as it is interpreted in a particular way as long as it is consistent. SQL Server has a globaldateformat settings like mdy, dmy, ymd (similar to nls_dateformat). I am not expert on sqlserver but trying to work/learn since past few months.
Tom Kyte
July 17, 2013 - 5:23 pm UTC

the only thing I can suggest is a small plsql function that iterates through a set of formats you would have stored in an array - trying each in order until one works.

but that is just about the scariest routine I can think of even writing. The corrupted data probability is astounding.

dateformat

Ravi B, July 16, 2013 - 8:48 pm UTC

Also, in SQLServer, ISDATE will determine if the date is a valid or not. If it is ambiguous it will return 0, else it returns 1. We dont have to give any format mask.
Tom Kyte
July 17, 2013 - 6:03 pm UTC

if you provide detailed specs on how it works - that'd be great. The logical it employs.

for example, what is:

01-jan-12

what date is that? almost ALL dates are ambiguous.

isdate seems to be reliant on some session settings, meaning is does not work the way you describe at all.

http://msdn.microsoft.com/en-us/library/ms187347.aspx

in order for it to work, it relies on a DATEFORMAT setting.


you are going to have to dig a bit deeper into the specification of this question - the functions you are using in sqlserver are not doing what you say they are doing.


it is demonstrating a small number of supported formats AND relies on a dateformat. we do the same thing.

AM vs PM vs EV [ from 4pm - 8pm]

Jac BA, February 24, 2014 - 11:12 pm UTC

Is there a way to to distinguish between Am/PM and EV using the FROM_TZ... at time zone 'US/Eastern', 'AM'?

Need to Convert varchar to date

NARESH mundra, February 01, 2018 - 8:36 pm UTC

Hi i have a Varchar field coming in this format "07/25/2017 08:00:00 AM", I have to convert it to date with AM and PM , How can i achieve it.
I tried this select TO_DATE(ARRIVAL_START_Date,'yyyy/mm/dd hh24:mi:ss') from Employee.


i got the following error

ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.

Connor McDonald
February 02, 2018 - 12:13 am UTC

SQL> select to_date('07/25/2017 08:00:00 AM','MM/DD/YYYY HH:MI:SS AM') from dual;

TO_DATE('07/25/2017
-------------------
25/07/2017 08:00:00

SQL>
SQL> select to_date('07/25/2017 08:00:00 PM','MM/DD/YYYY HH:MI:SS AM') from dual;

TO_DATE('07/25/2017
-------------------
25/07/2017 20:00:00