The second form of your query will work if you make your mark your function as deterministic, meaning that for a given set of input values, it will always return the same result.
With that set, Oracle will only run the conversion once for each parameters in the where
clause rather than for every row.
With this:
CREATE OR REPLACE FUNCTION TO_MINUTE_D (DATE_IN IN DATE)
RETURN NUMBER DETERMINISTIC AS
BEGIN
/* Minute 0 = 12/30/1899 12:00am */
RETURN
(TRUNC(DATE_IN, 'DD') - TO_DATE('12/30/1899', 'MM/DD/YYYY')) * 1440 +
TO_NUMBER(TO_CHAR(DATE_IN, 'HH24')) * 60 +
TO_NUMBER(TO_CHAR(DATE_IN, 'MI'));
END TO_MINUTE_D;
/
On a table filled with a large bunch of dummy rows (increasing ints), I get the following timings consistently:
SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD'));
no rows selected
Elapsed: 00:00:12.69
versus deterministic-annotated function:
SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE_D(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE_D(TO_DATE('2013-01-01', 'YYYY-MM-DD'));
no rows selected
Elapsed: 00:00:00.07
You should get very close to what you have with the values plugged in directly, and indexes on those columns can be used as if you'd plugged in literals.
(Putting the conversion function on the start|stop_minute
columns isn't a good idea in general as you've discovered, unless you have a function-based index on those that matches exactly.)
I understand your problem to be how to successfully convert the string into a DATE value. An undelimited string of integers is assumed to be Year-Month-Day order. (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.)
Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around.
http://msdn.microsoft.com/en-us/library/ms187928.aspx -- CAST and CONVERT
I changed the month to 8 to make it easier to double check. Using the CONVERT style option 3, you can do the following:
DECLARE @String VARCHAR(10);
DECLARE @DateValue DATE;
SET @String = '250809';
-- Convert your undelimited string DDMMYY into a DATE
-- First: Add / between the string parts.
SET @STRING = SUBSTRING(@String,1,2)+'/'+
SUBSTRING(@String,3,2)+'/'+SUBSTRING(@String,5,2);
-- Second: Convert using STYLE 3 to get DD/MM/YY interpretation
SELECT @DateValue = CONVERT(Date, @String, 3);
-- Using the DATE
-- Select the value in default Year-Month-Day
SELECT @DateValue AS DefaultFormat;
-- Select the value formatted as dd/mm/yy
SELECT CONVERT(VARCHAR(20),@DateValue,3) AS [DD/MM/YY];
The results of the last two selects are:
DefaultFormat
-------------
2009-08-25
DD/MM/YY
--------
25/08/09
To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. (I am sure that there are other workarounds and conversion styles that would work as well.)
Likewise when displaying the DATE as you desire, you need to use STYLE 3
Best Answer
To address the immediate question:
The CONVERT function works with various types. What kind of conversion to apply is determined principally by the types specified by the first argument (explicitly) and the second argument (implicitly as the type of the expression). The presence of the third argument does not automatically imply that the expression, second parameter, should be treated as a datetime argument, because there are other conversion algorithms that allow you to specify a style as the third argument of CONVERT – not just those between strings and dates/times/datetimes.
So, since
last_cko_date
is a number (asmallint
) and the target type is a string (char(8)
specifically), the conversion is made between a number and a string, not between a date and a string, even though you have specified 103, which is a style used for date and time conversion to/from a string.In order to resolve the problem of getting a date-formatted string out of an integer, therefore, you first need to represent the number as a date or datetime value. The value of 16911 looks to me like an offset from 1970-01-01, in days, so, assuming that, this is what you could do first:
That will give you a datetime value corresponding to the integer
last_cko_date
. Use that expression in the CONVERT function to return the date in the desired style:Or, since you actually want
mm/dd/yyyy
:Note also the
char(10)
instead ofchar(8)
. Both styles above return a 10-character string. Truncating it to 8 will remove the last digits of the year, as tpet has correctly noticed.A possibly better idea, however, would be to omit the conversion in SQL in the first place. Return the date to the client as is and let it do the job of presenting the data in the required format.