How to Understand How Value Converted to Date Format Implicitly

Unable to understand how value converted to date format implicitly

The String-to-Date Conversion Rules allow additional formatting rules (without any other modifiers being applied). So:

• MM also matches MON and MONTH;
• MON matches MONTH (and vice versa);
• RR matches RRRR; and
• The punctuation is optional.

So:

SELECT TO_DATE( '10AUGUST2016', 'DD-MM-RR'    ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUGUST2016', 'DD-MON-RR'   ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUGUST2016', 'DD-MONTH-RR' ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MM-RR'    ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MON-RR'   ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MONTH-RR' ) FROM DUAL;

All generate the date 2016-08-10T00:00:00.

You can prevent this by using the FX format model

FX

Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function:

• Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.

• The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks.

• Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeroes.

  When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well.

If any portion of the character argument violates any of these conditions, then Oracle returns an error message.

Then:

SELECT TO_DATE( '10-AUGUST-2016', 'FXDD-MM-RR'    ) FROM DUAL;

Gives: ORA-01858: a non-numeric character was found where a numeric was expected and would only match where an exact pattern match is found (although RR will still match RRRR).

I guess implicit conversion is taking place?

Yes, oracle is implicitly using TO_DATE( '10AUGUST2016', NLS_DATE_FORMAT ) to do the conversion.

If you use:

ALTER SESSION SET NLS_DATE_FORMAT = 'FXDD-MM-RR';

Then your insert will fail.

Mysql implicit conversion when comparing date column and datetime/time string

I don't think you are interpreting the docs correctly. The first page you quote is about comparing a DATETIME or TIMESTAMP to a constant. You are comparing a DATE to a constant, so this logic should apply:

Conversion of DATE values:

Conversion to a DATETIME or TIMESTAMP value adds a time part of '00:00:00' because the DATE value contains no time information.

So it's like comparing '2017-11-24 00:00:00' = '2017-11-24 00:00:01' which sensibly returns false.

Comparing DATEs to DATETIMEs/TIMESTAMPs is similar to comparing an int to a double in that the implicit conversion applies to the less precise value. You would not expect 42 to match 42.1, and likewise you shouldn't expect 2017-11-24 to match 2017-11-24 00:00:01.

How does SQL Server decide format for implicit datetime conversion?

This can depend on a variety of factors - the operating system's regional settings, the current user's language and dateformat settings. By default, Windows uses US English, and the user's settings are US English and MDY.

But here are some examples to show how this can change.

User is using BRITISH language settings:

-- works:
SET LANGUAGE BRITISH;
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04/13/2012');
GO

(Error)

Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data
type to a datetime data type resulted in an out-of-range value.

User is using Français:

-- works:
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04/13/2012');
GO

(Error)

Msg 242, Level 16, State 3, Line 1
La conversion d'un type de
données varchar en type de données datetime a créé une valeur hors
limites.

User is again using Français:

SET LANGUAGE FRENCH;

-- fails (proving that, contrary to popular belief, YYYY-MM-DD is not always safe):
SELECT CONVERT(DATETIME, '2012-04-30');
GO

(Error)

Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données
varchar en type de données datetime a créé une valeur hors limites.

User is using DMY instead of MDY:

SET LANGUAGE ENGLISH;
SET DATEFORMAT DMY;

-- works:
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04-30-2012');
GO

(Error)

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar
data type to a datetime data type resulted in an out-of-range value.

Your best bet, always, is to use ISO standard, non-regional, safe, unambiguous date formats. The two I typically recommend are:

YYYYMMDD                  - for date only.
YYYY-MM-DDTHH:MM:SS[.mmm] - for date + time, and yes that T is important.

None of these fail:

SET DATEFORMAT MDY;
SET LANGUAGE ENGLISH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET LANGUAGE BRITISH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET DATEFORMAT DMY;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');

Therefore, I strongly recommend that instead of letting users type in free text date formats (or that you use unreliable formats yourself), control your input strings and make sure they adhere to one of these safe formats. Then it won't matter what settings the user has or what the underlying regional settings are, your dates will always be interpreted as the dates they were intended to be. If you are currently letting users enter dates into a text field on a form, stop doing that and implement a calendar control or at least a pick list so you can ultimately control the string format that is passed back to SQL Server.

For some background, please read Tibor Karaszi's "The ultimate guide to the datetime datatypes" and my post "Bad Habits to Kick : Mis-handling date / range queries."

Oracle Implicit Conversion of String to date?

A1: In general yes, but take the way Oracle handles implicit type conversions into account. The To_Date function around the mydatefield column expects a string input, so Oracle implicitly converts mydatefield to a string with a format matching the NLS_DATE_FORMAT session setting (which defaults to DD-MON-RR). Once converted to a string the To_Date function then converts it back to a date again using the current NLS_DATE_FORMAT setting. The newly reconstituted date is then compared to the string '23-OCT-2015', but since dates and strings aren't directly comparable the string value gets implicitly converted to a date using the current NLS_DATE_FORMAT setting. Depending on the value of the NLS_DATE_FORMAT setting, the first implicit conversion is likely to lose information specifically any time portion AND the original century, since the default NLS_DATE_FORMAT uses only a two digit year RR and no time component.

A2: Possibly, but it's best not to rely on it.

Both relations are poor programming for a couple of reasons. First they both are affected by implicit type conversions from dates to strings (or vice versa). Second they are both attempting to compare dates with strings in a non canonical form. As such 10-DEC-15 is less than 23-OCT-2015 because 1 is less than 2. Also note the difference in the number of digits representing the year since the default NLS_DATE_FORMAT uses a two digit year.

The correct method would be to compare the date column (possibly truncated) to a date string explicitly converted to a date

WHERE mytable.mydatefield > TO_DATE('23-OCT-2015', 'DD-MON-YYYY')

OR with truncation:

WHERE trunc(mytable.mydatefield) > TO_DATE('23-OCT-2015', 'DD-MON-YYYY')

which removes the time component of the date field.

Implicit conversion of datatypes from varchar2 to date, number etc

You can do this, it's all about NLS_DATE_FORMAT.
But there is risk:

create table a(
  pk integer not null,
  dt varchar2(30)
);
create table b(
  pk integer not null,
  dt date
);
--
-- This example shows date truncation because of the nls_date_format
--
alter session set nls_date_format='DD-MON-YYYY';
insert into b values(1, sysdate);
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 11:53:05
insert into a select * from b;
select * from a;
-- 02-FEB-2021
delete b;
insert into b select * from a;
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 00:00:00

--
-- This example shows a working conversion
---
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
delete a;
delete b;
insert into b values(1, sysdate);
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 11:53:05
insert into a select * from b;
select * from a;
-- 2021-02-02 11:53:05
delete b;
insert into b select * from a;
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 11:53:05

--
-- This example shows a failure because of bad date mask.
--
alter session set nls_date_format='YYYY-MM-DD';
delete a;
delete b;
insert into b values(1, sysdate);
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 11:53:05
insert into a select * from b;
select * from a;
-- 2021-02-02
alter session set nls_date_format='Dy DD/MM HH24:MI:SS';
delete b;
insert into b select * from a;
-- ORA-01846: not a valid day of the week

In short, don't do it unless absolutely necessary.
If you have to do it, make sure you control nls_date_mask by setting it in your script prior to the SQL execution.

Implicit date format conversion

You infered well, ASP more accurately The ASP engine in your web server (IIS) is converting imlpicitily all your date values, based on the system regional settings in your Server.

Doesn't matter in which format do you have stored the date values in the database, ASP will allways convert it all. Exists two altenatives to correct this:

the use of LCID property

The LCID property specifies how dates, times, and currencies are formatted. Locale identifiers (LCIDs) are not the same for each geographical locale. Some locales format dates as YY-MM-DD, and some format dates as MM-DD-YYYY. The LCID property is read/write.

The LCID is supported in Session and in Response (this only supported in recent versions of windows) Object.

depending where you live or where your clients are from you can choose the LCID more appropiated to them in this list of LCID Numbers

More Information:

MSDN - Response.LCID

MSDN - Session.LCID

change the regional settings in your server

check this thread for more information

ASP Classic & SQL date out-of-range error

(check the quick fix part)

How to understand given sysdate is Date or Timestamp

Your procedure will only ever receive a date, because that is the data type of the formal parameter. When the procedure is called the caller can supply a date, or something that can be implicitly converted to a date (though they shouldn't; implicit conversions are generally a bad thing, particularly from strings).

The date data type includes time components. If you are being passed a date with a non-midnight time that you want to ignore, such as sysdate, you can use the trunc() function, with it's default 'DD' format; and you don't need to select from dual:

v_last_day := last_day(trunc(var_P_DATE));

If the caller passes in systimestamp then that will still be implcitly converted to a date by the time you see it - which means it loses any fractional seconds and time zone information, but retains hours, minutes and seconds.

Dates and timestamps do not have have any inherent human-readable format. A date can be displayed using various formats - see the documentation - either explicitly with to_char() and a format model, or implicitly using your session settings.

When you do

dbms_output.put_line(var_P_DATE);

you are doing an implicit conversion of the date value to a string, using the session's NLS_DATE_FORMAT setting. So, different users might see that in different formats. You have no control over that. If you want to see a specific format then specify that, e.g.:

dbms_output.put_line(to_char(var_P_DATE, 'YYYY-MM-DD'));

You also have no control over whether the caller sees that output - it's down to the application/client and its settings. It looks like you are probably only using it for debugging the comparison issue though, so that probably doesn't matter here.

So as a demonstration:

declare
  var_P_DATE date := sysdate;
  v_last_day date;
begin
  v_last_day := last_day(var_P_DATE);
  dbms_output.put_line(to_char(v_last_day, 'YYYY-MM-DD HH24:MI:SS'));
  v_last_day := last_day(trunc(var_P_DATE));
  dbms_output.put_line(to_char(v_last_day, 'YYYY-MM-DD HH24:MI:SS'));
end;
/

2021-02-28 09:59:02
2021-02-28 00:00:00

db<>fiddle demo

---

Related Topics