Re: to_date format

> currently, the '2006092117 string is only part of it. I am using
> to_date but i am also using other statements such as SUBSTR and INSTR
> to pull out parts of the overall string (eg of this:
> 2006092117_876_gted.log) i place 876 in a field and 2006092117 into a
> field. the format mask YYYYMMDDHH24 is giving me the date and time that
> i want. eg - 21/09/2006 17:00:00.
> the problem is when the HH is 00 - i would have thought it would have
> given me the result 00:00:00. However, no time is being displayed. The
> date is but it is just missing out the time.
>
> I did try placing ||'00' after the string and adjusting the format mask
> - but this was giving me the same results. As it gives me the minutes
> and seconds as default 00 values (this is determined in the regional
> settings of the local machine).
>
> Im using TOAD and so was wondering if that was not showing the date
> properly for midnight.

Maybe Toad is causing confusion here. Have you tried your efforts in SQL*Plus to see if things are shown correctly?

Also, in Toad and SQL*Plus, you have a default date format. The DATE datatype stores both the day and the time. However, the default date format in many cases shows just the date without the time. You can override the default with the ALTER SESSION command. But for your SQL statements in your applications, you will want to use the TO_CHAR function so that you are sure you get what you are looking for. The format mask you have been specifying 'YYYYMMDDHH24' is great for converting the string to the DATE datatype, but you'll also want a format mask when querying:

TO_CHAR(date_column,'YYMMDDHH24MISS')

The above is just an example.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown