MySQL – How to Format a Date Field into Different Languages in One Command

As a_horse indicated, select min(date)... is sufficient. The DECODE and multiple conversions just slow things down without accomplishing anything. Even as parts of queries, you should eliminate this needless processing.

As Phil indicated, the displaying of dates is controlled by the NLS_DATE_FORMAT parameter and can be altered to display dates as you wish. This does change how the queries run (unless there are missing format specifications or SQL injection is being done). Change the display format like this:

 ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';

When doing a to_date on a two digit year, use YY when you want the first two digits of the year to match the current year, but use RR when you want the first two digits to be 20 for years ending in 0-49 and 19 for years ending in 50-99. See the SQL Language Reference for more information:

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.

That is:

• If the specified two-digit year is 00 to 49, then

  • If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.

  • If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

• If the specified two-digit year is 50 to 99, then

  • If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

  • If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

The following examples demonstrate the behavior of the RR datetime format element.

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 
Year
---- 
1998 

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 
Year
----
2017

The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are:

yyyyMMdd
yyyyMMdd hh:nn:ss[.mmmmmmm]
yyyy-MM-ddThh:nn:ss[.mmmmmmm]
----------^ yes, that T is important!

Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:*

SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2013-11-13');

Result:

Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line 2
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Just changing the language (which any of your user sessions can do) forced SQL Server to interpret that as YYYY-DD-MM instead of YYYY-MM-DD. Similar things can happen with setting like DATEFORMAT. But these settings are literally ignored when using the above two formats.

Always, always, always use one of the above two formats. If you are passing a variable as a string, stop doing that. If you can't, check to make sure it passes ISDATE() first. If you are letting people type any date string into a form field, stop doing that, too. Use a date-picker or calendar control and dictate the format of the string before you pass it to SQL Server. Well, depending on the language, just keep it as a datetime value and don't convert it to a string at all.

• I can't reproduce your problem at all, btw...

Please read this post:

• Bad Habits to Kick : Mis-handling date/range queries

_{There is an exception: SELECT CONVERT(DATE, 'yyyy-mm-dd'); will not break. But I err on the side of consistency rather than using a format only in the one place where I know it doesn't break, and having to use a safer format everywhere else.}