Merge commit

sql/field.cc:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/lex.h:
  Auto merged
sql/protocol.cc:
  Auto merged
sql/set_var.cc:
  Auto merged
sql/set_var.h:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_class.h:
  Auto merged
sql/sql_yacc.yy:
  Auto merged

18 files changed, 1410 insertions, 493 deletions

diff --git a/sql/field.cc b/sql/field.cc
index 68c9922e887..b025f65a798 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -279,7 +279,8 @@ bool Field::get_date(TIME *ltime,bool fuzzydate)
   char buff[40];
   String tmp(buff,sizeof(buff),&my_charset_bin),tmp2,*res;
   if (!(res=val_str(&tmp,&tmp2)) ||
-      str_to_TIME(res->ptr(),res->length(),ltime,fuzzydate) == TIMESTAMP_NONE)
+      str_to_TIME(res->ptr(),res->length(),ltime,fuzzydate,current_thd)<=
+      WRONG_TIMESTAMP_FULL)
     return 1;
   return 0;
 }
@@ -289,7 +290,7 @@ bool Field::get_time(TIME *ltime)
   char buff[40];
   String tmp(buff,sizeof(buff),&my_charset_bin),tmp2,*res;
   if (!(res=val_str(&tmp,&tmp2)) ||
-      str_to_time(res->ptr(),res->length(),ltime))
+      str_to_time(res->ptr(),res->length(),ltime,current_thd))
     return 1;
   return 0;
 }
@@ -299,28 +300,29 @@ bool Field::get_time(TIME *ltime)
 void Field::store_time(TIME *ltime,timestamp_type type)
 {
   char buff[25];
+  String tmp((char*) buff,sizeof(buff),&my_charset_bin);
+  DATETIME_FORMAT *tmp_format= 0;
+  bool is_time_only= 0;
+
   switch (type)  {
   case TIMESTAMP_NONE:
+  case WRONG_TIMESTAMP_FULL:
     store("",0,&my_charset_bin);	// Probably an error
-    break;
+    return;
   case TIMESTAMP_DATE:
-    sprintf(buff,"%04d-%02d-%02d", ltime->year,ltime->month,ltime->day);
-    store(buff,10,&my_charset_bin);
+    tmp_format= &t_datetime_frm(current_thd, DATE_FORMAT_TYPE).datetime_format;
     break;
   case TIMESTAMP_FULL:
-    sprintf(buff,"%04d-%02d-%02d %02d:%02d:%02d",
-	    ltime->year,ltime->month,ltime->day,
-	    ltime->hour,ltime->minute,ltime->second);
-    store(buff,19,&my_charset_bin);
+    tmp_format=&t_datetime_frm(current_thd,DATETIME_FORMAT_TYPE).datetime_format;
     break;
   case TIMESTAMP_TIME:
-  {
-    ulong length= my_sprintf(buff, (buff, "%02d:%02d:%02d",
-				    ltime->hour,ltime->minute,ltime->second));
-    store(buff,(uint) length, &my_charset_bin);
+    tmp_format= &t_datetime_frm(current_thd, TIME_FORMAT_TYPE).datetime_format;
+    is_time_only= 1;
     break;
   }
-  }
+  make_datetime(&tmp, ltime, is_time_only, 0,
+		tmp_format->format, tmp_format->format_length, 1);
+  store(tmp.ptr(),tmp.length(),&my_charset_bin);
 }
 
 
@@ -2691,7 +2693,7 @@ Field_timestamp::Field_timestamp(char *ptr_arg, uint32 len_arg,
 
 int Field_timestamp::store(const char *from,uint len,CHARSET_INFO *cs)
 {
-  long tmp=(long) str_to_timestamp(from,len);
+  long tmp=(long) str_to_timestamp(from,len,current_thd);
 #ifdef WORDS_BIGENDIAN
   if (table->db_low_byte_first)
   {
@@ -3025,7 +3027,7 @@ int Field_time::store(const char *from,uint len,CHARSET_INFO *cs)
   TIME ltime;
   long tmp;
   int error= 0;
-  if (str_to_time(from,len,&ltime))
+  if (str_to_time(from,len,&ltime,current_thd))
   {
     tmp=0L;
     error= 1;
@@ -3134,19 +3136,25 @@ longlong Field_time::val_int(void)
 String *Field_time::val_str(String *val_buffer,
 			    String *val_ptr __attribute__((unused)))
 {
+  TIME ltime;
   val_buffer->alloc(16);
   long tmp=(long) sint3korr(ptr);
   const char *sign="";
+  ltime.neg= 0;
   if (tmp < 0)
   {
     tmp= -tmp;
-    sign= "-";
-  }
-  long length= my_sprintf((char*) val_buffer->ptr(),
-			  ((char*) val_buffer->ptr(),"%s%02d:%02d:%02d",
-			   sign,(int) (tmp/10000), (int) (tmp/100 % 100),
-			   (int) (tmp % 100)));
-  val_buffer->length(length);
+    ltime.neg= 1;
+  }
+  DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				(current_thd, TIME_FORMAT_TYPE).datetime_format);
+  ltime.day= (uint) 0;
+  ltime.hour= (uint) (tmp/10000);
+  ltime.minute= (uint) (tmp/100 % 100);
+  ltime.second= (uint) (tmp % 100);
+  make_datetime(val_buffer, &ltime, 0, 0,
+		tmp_format->format,
+		tmp_format->format_length, 1);
   return val_buffer;
 }
 
@@ -3312,7 +3320,7 @@ int Field_date::store(const char *from, uint len,CHARSET_INFO *cs)
   TIME l_time;
   uint32 tmp;
   int error= 0;
-  if (str_to_TIME(from,len,&l_time,1) == TIMESTAMP_NONE)
+  if (str_to_TIME(from,len,&l_time,1,current_thd) <= WRONG_TIMESTAMP_FULL)
   {
     tmp=0;
     error= 1;
@@ -3422,6 +3430,7 @@ longlong Field_date::val_int(void)
 String *Field_date::val_str(String *val_buffer,
 			    String *val_ptr __attribute__((unused)))
 {
+  TIME ltime;
   val_buffer->alloc(field_length);
   val_buffer->length(field_length);
   int32 tmp;
@@ -3431,9 +3440,15 @@ String *Field_date::val_str(String *val_buffer,
   else
 #endif
     longget(tmp,ptr);
-  sprintf((char*) val_buffer->ptr(),"%04d-%02d-%02d",
-	  (int) ((uint32) tmp/10000L % 10000), (int) ((uint32) tmp/100 % 100),
-	  (int) ((uint32) tmp % 100));
+  DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				(current_thd, DATE_FORMAT_TYPE).datetime_format);
+  ltime.neg= 0;
+  ltime.year= (int) ((uint32) tmp/10000L % 10000);
+  ltime.month= (int) ((uint32) tmp/100 % 100);
+  ltime.day= (int) ((uint32) tmp % 100);
+  make_datetime(val_buffer, &ltime, 0, 0,
+		tmp_format->format,
+		tmp_format->format_length, 1);
   return val_buffer;
 }
 
@@ -3492,7 +3507,7 @@ int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs)
   TIME l_time;
   long tmp;
   int error= 0;
-  if (str_to_TIME(from,len,&l_time,1) == TIMESTAMP_NONE)
+  if (str_to_TIME(from,len,&l_time,1,current_thd) <= WRONG_TIMESTAMP_FULL)
   {
     tmp=0L;
     error= 1;
@@ -3661,7 +3676,7 @@ void Field_newdate::sql_type(String &res) const
 
 int Field_datetime::store(const char *from,uint len,CHARSET_INFO *cs)
 {
-  longlong tmp=str_to_datetime(from,len,1);
+  longlong tmp=str_to_datetime(from,len,1,current_thd);
 #ifdef WORDS_BIGENDIAN
   if (table->db_low_byte_first)
   {
diff --git a/sql/item.cc b/sql/item.cc
index 4de4951cb51..32fcba1bf2d 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -46,7 +46,7 @@ Item::Item():
   collation.set(default_charset(), DERIVATION_COERCIBLE);
   name= 0;
   decimals= 0; max_length= 0;
-  THD *thd= current_thd;
+  thd= current_thd;
   next= thd->free_list;			// Put in free list
   thd->free_list= this;
   /*
@@ -69,7 +69,7 @@ Item::Item():
   Used for duplicating lists in processing queries with temporary
   tables
 */
-Item::Item(THD *thd, Item &item):
+Item::Item(THD *c_thd, Item &item):
   str_value(item.str_value),
   name(item.name),
   max_length(item.max_length),
@@ -82,7 +82,8 @@ Item::Item(THD *thd, Item &item):
   fixed(item.fixed),
   collation(item.collation)
 {
-  next=thd->free_list;			// Put in free list
+  next=c_thd->free_list;			// Put in free list
+  thd= c_thd;
   thd->free_list= this;
 }
 
@@ -174,7 +175,8 @@ bool Item::get_date(TIME *ltime,bool fuzzydate)
   char buff[40];
   String tmp(buff,sizeof(buff), &my_charset_bin),*res;
   if (!(res=val_str(&tmp)) ||
-      str_to_TIME(res->ptr(),res->length(),ltime,fuzzydate) == TIMESTAMP_NONE)
+      str_to_TIME(res->ptr(),res->length(),ltime,fuzzydate, thd) <= 
+      WRONG_TIMESTAMP_FULL)
   {
     bzero((char*) ltime,sizeof(*ltime));
     return 1;
@@ -192,7 +194,7 @@ bool Item::get_time(TIME *ltime)
   char buff[40];
   String tmp(buff,sizeof(buff),&my_charset_bin),*res;
   if (!(res=val_str(&tmp)) ||
-      str_to_time(res->ptr(),res->length(),ltime))
+      str_to_time(res->ptr(),res->length(),ltime, thd))
   {
     bzero((char*) ltime,sizeof(*ltime));
     return 1;
@@ -673,30 +675,28 @@ String *Item_param::query_val_str(String* str)
     }
     else
     {
-      char buff[25];
+      DATETIME_FORMAT *tmp_format= 0;
+      bool is_time_only= 0;
       
       switch (ltime.time_type)  {
         case TIMESTAMP_NONE:
+        case WRONG_TIMESTAMP_FULL:
           break;
         case TIMESTAMP_DATE:
-          sprintf(buff, "%04d-%02d-%02d", 
-                        ltime.year,ltime.month,ltime.day);
-          str->append(buff, 10);
+	  tmp_format= &t_datetime_frm(thd, DATE_FORMAT_TYPE).datetime_format;
           break;
         case TIMESTAMP_FULL:
-          sprintf(buff, "%04d-%02d-%02d %02d:%02d:%02d",
- 	                ltime.year,ltime.month,ltime.day,
-	                ltime.hour,ltime.minute,ltime.second);
-          str->append(buff, 19);
+	  tmp_format= &t_datetime_frm(thd, DATETIME_FORMAT_TYPE).datetime_format;
           break;
         case TIMESTAMP_TIME:
         {
-          sprintf(buff, "%02d:%02d:%02d",
-	  	            ltime.hour,ltime.minute,ltime.second);
-          str->append(buff, 8);
+	  tmp_format= &t_datetime_frm(thd, TIME_FORMAT_TYPE).datetime_format;
+	  is_time_only= 1;
           break;
-        }
+        }	
       }
+      make_datetime(str, &ltime, is_time_only, 0,
+		    tmp_format->format, tmp_format->format_length, 0);
     }
     str->append("'");
   }
diff --git a/sql/item.h b/sql/item.h
index 517d3f311e5..c738f92124f 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -113,6 +113,14 @@ public:
   my_bool with_sum_func;
   my_bool fixed;                        /* If item fixed with fix_fields */
   DTCollation collation;
+
+
+  /*
+    thd is current_thd value. Like some other Item's fields it
+    will be a problem for using one Item in different threads
+    (as stored procedures may want to do in the future)
+  */
+  THD *thd;
   
   // alloc & destruct is done as start of select using sql_alloc
   Item();
@@ -124,7 +132,7 @@ public:
      top AND/OR ctructure of WHERE clause to protect it of
      optimisation changes in prepared statements
   */
-  Item(THD *thd, Item &item);
+  Item(THD *c_thd, Item &item);
   virtual ~Item() { name=0; }		/*lint -e1509 */
   void set_name(const char *str,uint length, CHARSET_INFO *cs);
   void init_make_field(Send_field *tmp_field,enum enum_field_types type);
diff --git a/sql/item_create.cc b/sql/item_create.cc
index b1173b9c7b8..fce59d68c1f 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -701,3 +701,8 @@ Item *create_func_maketime(Item* a,Item* b,Item* c)
 {
   return new Item_func_maketime(a, b, c);
 }
+
+Item *create_func_str_to_date(Item* a,Item* b)
+{
+  return new Item_func_str_to_date(a, b);
+}
diff --git a/sql/item_create.h b/sql/item_create.h
index c75f4404bad..5d6cbe1d58f 100644
--- a/sql/item_create.h
+++ b/sql/item_create.h
@@ -149,3 +149,4 @@ Item *create_func_addtime(Item* a,Item* b);
 Item *create_func_subtime(Item* a,Item* b);
 Item *create_func_timediff(Item* a,Item* b);
 Item *create_func_maketime(Item* a,Item* b,Item* c);
+Item *create_func_str_to_date(Item* a,Item* b);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 31ce2ad9cdc..4009256ee17 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -57,82 +57,544 @@ static String day_names[] =
   String("Sunday",	&my_charset_latin1)
 };
 
-enum date_time_format_types 
-{ 
-  TIME_ONLY= 0, TIME_MICROSECOND,
-  DATE_ONLY, DATE_TIME, DATE_TIME_MICROSECOND
-};
-
-typedef struct date_time_format 
+uint check_names(String *arr,int item_count,const char *val_ptr,
+		 const char *val_end, uint *val, bool check_part)
 {
-  const char* format_str;
-  uint length;
-};
+  for (int i= 0; i < item_count; i++)
+  {
+    String *tmp=&arr[i];
+    if (!my_strnncoll(&my_charset_latin1, 
+		      (const uchar *) val_ptr, 3, 
+		      (const uchar *) tmp->ptr(), 3))
+    {
+      if (check_part)
+      {
+	*val= i+1;
+	return 3;
+      }
 
-static struct date_time_format date_time_formats[]=
+      int part_len= tmp->length() - 3;
+      int val_len= val_end - val_ptr - 3;
+      if (val_len < part_len)
+	return 0;
+      val_ptr+=3;
+      if (!my_strnncoll(&my_charset_latin1, 
+			(const uchar *) val_ptr, part_len, 
+			(const uchar *) tmp->ptr() + 3, part_len))
+      {
+	*val= i+1;
+	return tmp->length();
+      }
+      return 0;
+    }	    
+  }
+  return 0;
+}
+
+uint check_val_is_digit(const char *ptr, uint val_len, uint digit_count)
 {
-  {"%s%02d:%02d:%02d", 10},
-  {"%s%02d:%02d:%02d.%06d", 17},
-  {"%04d-%02d-%02d", 10},
-  {"%04d-%02d-%02d %02d:%02d:%02d", 19},
-  {"%04d-%02d-%02d %02d:%02d:%02d.%06d", 26}
-};
+  uint i;
+  uint verify_count= (val_len < digit_count ? val_len : digit_count);
+  uint digit_found= 0;
+  for (i= 0; i < verify_count; i++)
+  {
+    if (!my_isdigit(&my_charset_latin1, *(ptr+i)))
+      break;
+    digit_found++;
+  }
+  return digit_found;
+}
 
 
 /*
-  OPTIMIZATION TODO:
-   - Replace the switch with a function that should be called for each
-     date type.
-   - Remove sprintf and opencode the conversion, like we do in
-     Field_datetime.
+  Extract datetime value to TIME struct from string value
+  according to format string. 
 */
-
-String *make_datetime(String *str, TIME *ltime, 
-		      enum date_time_format_types format)
+bool extract_datetime(const char *str_val, uint str_val_len,
+		      const char *str_format, uint str_format_len,
+		      TIME *l_time)
 {
-  char *buff;
+  char intbuff[15];
+  int weekday= 0, yearday= 0, daypart= 0, len;
+  int val_len= 0;
+  int week_number= -1;
+  ulong length;
   CHARSET_INFO *cs= &my_charset_bin;
-  uint length= date_time_formats[format].length + 32;
-  const char* format_str= date_time_formats[format].format_str;
+  int err= 0;
+  bool usa_time= 0;
+  bool sunday_first= 0;
+  const char *rT_format= "%H:%i:%s";
+  uint part_len= 0;
+  const char *val_ptr=str_val;
+  const char *val_end= str_val + str_val_len;
+  const char *ptr=str_format;
+  const char *end=ptr+ str_format_len;
+
+  DBUG_ENTER("extract_datetime");
+  for (; ptr != end && val_ptr != val_end; ptr++)
+  {
+    if (*ptr == '%' && ptr+1 != end)
+    {
+      val_len= val_end - val_ptr;
+      char *val_end1= (char *) val_end;
+      switch (*++ptr) {
+      case 'h':
+      case 'I':
+      case 'H':
+	l_time->hour= my_strntoll(cs, val_ptr, 
+				  2, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 2))
+	  return 1;
+	usa_time= (*ptr == 'I' || *ptr == 'h');
+	val_ptr+=2;
+	break;
+      case 'k':
+      case 'l':
+	l_time->hour= my_strntoll(cs, val_ptr, 
+				  2, 10, &val_end1, &err);
+	if (err)
+	  return 1;
+	usa_time= (*ptr == 'l');
+	val_ptr= val_end1;
+	break;
+      case 'e':
+	l_time->day= my_strntoll(cs, val_ptr, 
+				 2, 10, &val_end1, &err);
+	if (err)
+	  return 1;
+	val_ptr= val_end1;
+	break;
+      case 'c':
+	l_time->month= my_strntoll(cs, val_ptr, 
+				   2, 10, &val_end1, &err);
+	if (err)
+	  return 1;
+	val_ptr= val_end1;
+	break;
+      case 'Y':
+	l_time->year= my_strntoll(cs, val_ptr, 
+				  4, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 4))
+	  return 1;
+	val_ptr+=4;
+	break;
+      case 'y':
+	l_time->year= my_strntoll(cs, val_ptr, 
+				  2, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 2))
+	  return 1;
+	l_time->year+= (l_time->year < YY_PART_YEAR ? 2000 : 1900);
+	val_ptr+=2;
+	break;
+      case 'm':
+	l_time->month= my_strntoll(cs, val_ptr, 
+				   2, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 2))
+	  return 1;
+	val_ptr+=2;
+	break;
+      case 'd':
+	l_time->day= my_strntoll(cs, val_ptr, 
+				 2, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 2))
+	  return 1;
+	val_ptr+=2;
+	break;
+      case 'D':
+	l_time->day= my_strntoll(cs, val_ptr, 
+				 2, 10, &val_end1, &err);
+	if (err || (val_len < val_end1 - val_ptr + 2))
+	  return 1;
+	val_ptr= val_end1 + 2;
+	break;
+      case 'i':
+	l_time->minute=my_strntoll(cs, val_ptr, 
+				   2, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 2))
+	  return 1;
+	val_ptr+=2;
+	break;
+      case 's':
+      case 'S':
+	l_time->second= my_strntoll(cs, val_ptr, 
+				    2, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 2))
+	  return 1;
+	val_ptr+=2;
+	break;
+      case 'M':
+	if (val_len < 3 ||
+	    !(part_len= check_names(month_names, 12 , val_ptr,
+				    val_end, &l_time->month, 0)))
+	  return 1;
+	val_ptr+= part_len;
+	break;
+      case 'b':
+	if (val_len < 3 ||
+	    !(part_len= check_names(month_names, 12 , val_ptr,
+				    val_end,(uint *) &l_time->month, 1)))
+	  return 1;
+	val_ptr+= part_len;
+	break;
+      case 'W':
+	if (val_len < 3 || 
+	    !(part_len= check_names(day_names, 7 , val_ptr,
+				    val_end,(uint *) &weekday, 0)))
+	  return 1;
+	val_ptr+= part_len;
+	break;
+      case 'a':
+	if (val_len < 3 || 
+	    !(part_len= check_names(day_names, 7 , val_ptr,
+				    val_end,(uint *) &weekday, 1)))
+	  return 1;
+	val_ptr+= part_len;
+	break;
+      case 'w':
+	weekday= my_strntoll(cs, val_ptr, 1, 10, &val_end1, &err);
+	if (err)
+	  return 1;
+	val_ptr++;
+	break;
+      case 'j':
+	yearday= my_strntoll(cs, val_ptr, 3, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 3))
+	  return 1;
+	val_ptr+=3;
+	break;
+      case 'f':
+	l_time->second_part= my_strntoll(cs, val_ptr, 3, 10, &val_end1, &err);
+	if (err)
+	  return 1;
+	val_ptr= val_end1;
+	break;
+      case 'p':
+	if (val_len < 2)
+	  return 1;
+	if (!my_strnncoll(&my_charset_latin1, 
+			  (const uchar *) val_ptr, 2, 
+			  (const uchar *) "PM", 2))
+	{
+	  daypart= 12;
+	  val_ptr+= 2;
+	}
+	break;
+      case 'U':
+	week_number= my_strntoll(cs, val_ptr, 2, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 2))
+	  return 1;
+	sunday_first= 1;
+	val_ptr+=2;
+	break;
+      case 'u':
+	week_number= my_strntoll(cs, val_ptr, 2, 10, &val_end1, &err);
+	if (err || (val_end1 - val_ptr != 2))
+	  return 1;
+	sunday_first=0;
+	val_ptr+=2;
+	break;
+      case 'r':
+      case 'T':
+	usa_time= (*ptr == 'r');
+	if (extract_datetime(val_ptr, val_end-val_ptr,
+			     rT_format, strlen(rT_format),
+			     l_time))
+	  return 1;
+	val_ptr+=8;
+	break;
+      default:
+	if (*val_ptr != *ptr)
+	  return 1;
+	val_ptr++;
+      }
+    }
+    else
+    {
+      if (*val_ptr != *ptr)
+	return 1;
+      val_ptr++;
+    }
+  }
+  if (usa_time)
+  {
+    if (l_time->hour > 12 || l_time->hour < 1)
+      return 1;
+    l_time->hour= l_time->hour%12+daypart;
+  }
 
-  if (str->alloc(length))
-    return 0;
+  if (yearday > 0)
+  {
+    uint days= calc_daynr(l_time->year,1,1) +  yearday - 1;
+    if (days > 0 || days < MAX_DAY_NUMBER)
+    {
+      get_date_from_daynr(days,&l_time->year,&l_time->month,&l_time->day);
+    }
+  }
 
-  buff= (char*) str->ptr();
-  switch (format) {
-  case TIME_ONLY:
-    length= cs->cset->snprintf(cs, buff, length, format_str, ltime->neg ? "-" : "",
-			       ltime->hour, ltime->minute, ltime->second);
-    break;
-  case TIME_MICROSECOND:
-    length= cs->cset->snprintf(cs, buff, length, format_str, ltime->neg ? "-" : "",
-			       ltime->hour, ltime->minute, ltime->second,
-			       ltime->second_part);
-    break;
-  case DATE_ONLY:
-    length= cs->cset->snprintf(cs, buff, length, format_str,
-			       ltime->year, ltime->month, ltime->day);
-    break;
-  case DATE_TIME:
-    length= cs->cset->snprintf(cs, buff, length, format_str,
-			       ltime->year, ltime->month, ltime->day,
-			       ltime->hour, ltime->minute, ltime->second);
-    break;
-  case DATE_TIME_MICROSECOND:
-    length= cs->cset->snprintf(cs, buff, length, format_str,
-			       ltime->year, ltime->month, ltime->day,
-			       ltime->hour, ltime->minute, ltime->second,
-			       ltime->second_part);
-    break;
-  default:
-    return 0;
+  if (week_number >= 0 && weekday)
+  {
+    int days= calc_daynr(l_time->year,1,1);
+    uint weekday_b;
+    
+    if (weekday > 7 || weekday < 0)
+	return 1;
+    if (sunday_first)
+      weekday = weekday%7;
+
+    if (week_number == 53)
+    {
+      days+= (week_number - 1)*7;
+      weekday_b= calc_weekday(days, sunday_first);
+      weekday = weekday - weekday_b - !sunday_first;
+      days+= weekday;
+    }
+    else if (week_number == 0)
+    {
+      weekday_b= calc_weekday(days, sunday_first);
+      weekday = weekday - weekday_b - !sunday_first;
+      days+= weekday;
+    }
+    else
+    {
+      days+= (week_number - !sunday_first)*7;
+      weekday_b= calc_weekday(days, sunday_first);
+      weekday =weekday - weekday_b - !sunday_first;
+      days+= weekday;
+    }
+    if (days > 0 || days < MAX_DAY_NUMBER)
+    {
+      get_date_from_daynr(days,&l_time->year,&l_time->month,&l_time->day);
+    }
   }
 
-  str->length(length);
-  str->set_charset(cs);
+  if (l_time->month > 12 || l_time->day > 31 || l_time->hour > 23 || 
+      l_time->minute > 59 || l_time->second > 59)
+    return 1;
+
+  DBUG_RETURN(0);
+}
+
+
+
+/*
+  Print datetime string from TIME struct
+  according to format string. 
+*/
+
+
+String *make_datetime(String *str, TIME *l_time,
+		      const bool is_time_only,
+		      const bool add_second_frac,
+		      const char *ptr, uint format_length,
+		      bool set_len_to_zero)
+{
+  char intbuff[15];
+  uint days_i;
+  uint hours_i;
+  uint weekday;
+  ulong length;
+  if (set_len_to_zero)
+    str->length(0);
+  if (l_time->neg)
+    str->append("-", 1);
+  const char *end=ptr+format_length;
+  for (; ptr != end ; ptr++)
+  {
+    if (*ptr != '%' || ptr+1 == end)
+      str->append(*ptr);
+    else
+    {
+      switch (*++ptr) {
+      case 'M':
+	if (!l_time->month)
+	  return 0;
+	str->append(month_names[l_time->month-1]);
+	break;
+      case 'b':
+	if (!l_time->month)
+	  return 0;
+	str->append(month_names[l_time->month-1].ptr(),3);
+	break;
+      case 'W':
+	if (is_time_only)
+	  return 0;
+	weekday=calc_weekday(calc_daynr(l_time->year,l_time->month,l_time->day),0);
+	str->append(day_names[weekday]);
+	break;
+      case 'a':
+	if (is_time_only)
+	  return 0;
+	weekday=calc_weekday(calc_daynr(l_time->year,l_time->month,l_time->day),0);
+	str->append(day_names[weekday].ptr(),3);
+	break;
+      case 'D':
+	if (is_time_only)
+	  return 0;
+	length= int10_to_str(l_time->day, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
+	if (l_time->day >= 10 &&  l_time->day <= 19)
+	  str->append("th");
+	else
+	{
+	  switch (l_time->day %10) {
+	  case 1:
+	    str->append("st",2);
+	    break;
+	  case 2:
+	    str->append("nd",2);
+	    break;
+	  case 3:
+	    str->append("rd",2);
+	    break;
+	  default:
+	    str->append("th",2);
+	    break;
+	  }
+	}
+	break;
+      case 'Y':
+	length= int10_to_str(l_time->year, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 4, '0');
+	break;
+      case 'y':
+	length= int10_to_str(l_time->year%100, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+	break;
+      case 'm':
+	length= int10_to_str(l_time->month, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+	break;
+      case 'c':
+	length= int10_to_str(l_time->month, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
+	break;
+      case 'd':
+	length= int10_to_str(l_time->day, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+	break;
+      case 'e':
+	length= int10_to_str(l_time->day, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
+	break;
+      case 'f':
+	length= int10_to_str(l_time->second_part, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 6, '0');
+	break;
+      case 'H':
+	length= int10_to_str(l_time->hour, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+	break;
+      case 'h':
+      case 'I':
+	days_i= l_time->hour/24;
+	hours_i= (l_time->hour%24 + 11)%12+1 + 24*days_i;
+	length= int10_to_str(hours_i, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+	break;
+      case 'i':					/* minutes */
+	length= int10_to_str(l_time->minute, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+	break;
+      case 'j':
+	if (is_time_only)
+	  return 0;
+	length= int10_to_str(calc_daynr(l_time->year,l_time->month,l_time->day) - 
+		     calc_daynr(l_time->year,1,1) + 1, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 3, '0');
+	break;
+      case 'k':
+	length= int10_to_str(l_time->hour, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
+	break;
+      case 'l':
+	days_i= l_time->hour/24;
+	hours_i= (l_time->hour%24 + 11)%12+1 + 24*days_i;
+	length= int10_to_str(hours_i, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
+	break;
+      case 'p':
+	hours_i= l_time->hour%24;
+	str->append(hours_i < 12 ? "AM" : "PM",2);
+	break;
+      case 'r':
+	length= my_sprintf(intbuff, 
+		   (intbuff, 
+		    (l_time->hour < 12) ? "%02d:%02d:%02d AM" : "%02d:%02d:%02d PM",
+		    (l_time->hour+11)%12+1,
+		    l_time->minute,
+		    l_time->second));
+	str->append(intbuff, length);
+	break;
+      case 'S':
+      case 's':
+	length= int10_to_str(l_time->second, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+	if (add_second_frac)
+	{
+	  str->append(".", 1);
+	  length= int10_to_str(l_time->second_part, intbuff, 10) - intbuff;
+	  str->append_with_prefill(intbuff, length, 6, '0');
+	}
+	break;
+      case 'T':
+	length= my_sprintf(intbuff, 
+		   (intbuff, 
+		    "%02d:%02d:%02d", 
+		    l_time->hour, 
+		    l_time->minute,
+		    l_time->second));
+	str->append(intbuff, length);
+	break;
+      case 'U':
+      case 'u':
+      {
+	uint year;
+	if (is_time_only)
+	  return 0;
+	length= int10_to_str(calc_week(l_time, 0, (*ptr) == 'U', &year),
+		     intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+      }
+      break;
+      case 'v':
+      case 'V':
+      {
+	uint year;
+	if (is_time_only)
+	  return 0;
+	length= int10_to_str(calc_week(l_time, 1, (*ptr) == 'V', &year),
+		     intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
+      }
+      break;
+      case 'x':
+      case 'X':
+      {
+	uint year;
+	if (is_time_only)
+	  return 0;
+	(void) calc_week(l_time, 1, (*ptr) == 'X', &year);
+	length= int10_to_str(year, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 4, '0');
+      }
+      break;
+      case 'w':
+	if (is_time_only)
+	  return 0;
+	weekday=calc_weekday(calc_daynr(l_time->year,l_time->month,l_time->day),1);
+	length= int10_to_str(weekday, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
+	break;
+      default:
+	str->append(*ptr);
+	break;
+      }
+    }
+  }
   return str;
 }
 
+
 /*
 ** Get a array of positive numbers from a string object.
 ** Each number is separated by 1 non digit character
@@ -346,7 +808,7 @@ longlong Item_func_year::val_int()
 longlong Item_func_unix_timestamp::val_int()
 {
   if (arg_count == 0)
-    return (longlong) current_thd->query_start();
+    return (longlong) thd->query_start();
   if (args[0]->type() == FIELD_ITEM)
   {						// Optimize timestamp field
     Field *field=((Item_field*) args[0])->field;
@@ -358,7 +820,7 @@ longlong Item_func_unix_timestamp::val_int()
   {
     return 0; /* purecov: inspected */
   }
-  return (longlong) str_to_timestamp(str->ptr(),str->length());
+  return (longlong) str_to_timestamp(str->ptr(),str->length(), thd);
 }
 
 
@@ -522,22 +984,26 @@ static bool get_interval_value(Item *args,interval_type int_type,
 
 String *Item_date::val_str(String *str)
 {
+  DATETIME_FORMAT *tmp_format;
+  TIME ltime;
   ulong value=(ulong) val_int();
   if (null_value)
-    return (String*) 0;
-  if (!value)					// zero daynr
-  {
-    str->copy("0000-00-00",10,&my_charset_latin1,default_charset());
+    goto null_date;
+
+  ltime.year=	(value/10000L) % 10000;
+  ltime.month=	(value/100)%100;
+  ltime.day=	(value%100);
+  ltime.neg=0;
+  ltime.time_type=TIMESTAMP_DATE;
+
+  tmp_format= &t_datetime_frm(thd, DATE_FORMAT_TYPE).datetime_format;
+  if (make_datetime(str, &ltime, 0, 0,
+		    tmp_format->format, tmp_format->format_length, 1))
     return str;
-  }
-  
-  char tmpbuff[11];
-  sprintf(tmpbuff,"%04d-%02d-%02d",
-	  (int) (value/10000L) % 10000,
-	  (int) (value/100)%100,
-	  (int) (value%100));
-  str->copy(tmpbuff,10,&my_charset_latin1,default_charset());
-  return str;
+
+  null_value= 1;
+null_date:
+  return 0;
 }
 
 
@@ -577,7 +1043,7 @@ void Item_func_curdate::fix_length_and_dec()
   decimals=0; 
   max_length=10*default_charset()->mbmaxlen;
 
-  store_now_in_tm(current_thd->query_start(),&start);
+  store_now_in_tm(thd->query_start(),&start);
   
   value=(longlong) ((ulong) ((uint) start.tm_year+1900)*10000L+
 		    ((uint) start.tm_mon+1)*100+
@@ -632,22 +1098,27 @@ String *Item_func_curtime::val_str(String *str)
 void Item_func_curtime::fix_length_and_dec()
 {
   struct tm start;
-  CHARSET_INFO *cs= default_charset();
+  DATETIME_FORMAT *tmp_format;
+  String tmp((char*) buff,sizeof(buff),default_charset());
+  TIME ltime;
 
   decimals=0; 
-  max_length=8*cs->mbmaxlen;
-  collation.set(cs);
-  
-  store_now_in_tm(current_thd->query_start(),&start);
-  
+  store_now_in_tm(thd->query_start(),&start);
   value=(longlong) ((ulong) ((uint) start.tm_hour)*10000L+
 		    (ulong) (((uint) start.tm_min)*100L+
 			     (uint) start.tm_sec));
-
-  buff_length=cs->cset->snprintf(cs,buff,sizeof(buff),"%02d:%02d:%02d",
-				 (int) start.tm_hour,
-				 (int) start.tm_min,
-				 (int) start.tm_sec);
+  ltime.day=	0;
+  ltime.hour=	start.tm_hour;
+  ltime.minute=	start.tm_min;
+  ltime.second=	start.tm_sec;
+  ltime.second_part= 0;
+  ltime.neg= 0;
+  ltime.time_type= TIMESTAMP_TIME;
+  tmp_format= &t_datetime_frm(thd, TIME_FORMAT_TYPE).datetime_format;
+  make_datetime(&tmp, &ltime, 0, 0,
+		tmp_format->format, tmp_format->format_length, 1);
+  buff_length= tmp.length();
+  max_length= buff_length; 
 }
 
 
@@ -681,14 +1152,11 @@ String *Item_func_now::val_str(String *str)
 void Item_func_now::fix_length_and_dec()
 {
   struct tm start;
-  CHARSET_INFO *cs= &my_charset_bin;
+  DATETIME_FORMAT *tmp_format;
+  String tmp((char*) buff,sizeof(buff),&my_charset_bin);
   
   decimals=0;
-  max_length=19*cs->mbmaxlen;
-  collation.set(cs);
-
-  store_now_in_tm(current_thd->query_start(),&start);
-  
+  store_now_in_tm(thd->query_start(),&start);
   value=((longlong) ((ulong) ((uint) start.tm_year+1900)*10000L+
 		     (((uint) start.tm_mon+1)*100+
 		      (uint) start.tm_mday))*(longlong) 1000000L+
@@ -696,14 +1164,6 @@ void Item_func_now::fix_length_and_dec()
 		     (ulong) (((uint) start.tm_min)*100L+
 			    (uint) start.tm_sec)));
   
-  buff_length= (uint) cs->cset->snprintf(cs,buff, sizeof(buff),
-				   "%04d-%02d-%02d %02d:%02d:%02d",
-				   ((int) (start.tm_year+1900)) % 10000,
-				   (int) start.tm_mon+1,
-				   (int) start.tm_mday,
-				   (int) start.tm_hour,
-				   (int) start.tm_min,
-				   (int) start.tm_sec);
   /* For getdate */
   ltime.year=	start.tm_year+1900;
   ltime.month=	start.tm_mon+1;
@@ -711,9 +1171,15 @@ void Item_func_now::fix_length_and_dec()
   ltime.hour=	start.tm_hour;
   ltime.minute=	start.tm_min;
   ltime.second=	start.tm_sec;
-  ltime.second_part=0;
-  ltime.neg=0;
-  ltime.time_type=TIMESTAMP_FULL;
+  ltime.second_part= 0;
+  ltime.neg= 0;
+  ltime.time_type= TIMESTAMP_FULL;
+
+  tmp_format= &t_datetime_frm(thd, DATETIME_FORMAT_TYPE).datetime_format;
+  make_datetime(&tmp, &ltime, 0, 0,
+		tmp_format->format, tmp_format->format_length, 1);
+  buff_length= tmp.length();
+  max_length= buff_length;
 }
 
 bool Item_func_now::get_date(TIME *res,
@@ -754,22 +1220,36 @@ void Item_func_now_utc::store_now_in_tm(time_t now, struct tm *now_tm)
 
 String *Item_func_sec_to_time::val_str(String *str)
 {
-  char buff[23*2];
-  const char *sign="";
   longlong seconds=(longlong) args[0]->val_int();
-  ulong length;
+  uint sec;
+
+  DATETIME_FORMAT *tmp_format;
+  TIME ltime;
+
   if ((null_value=args[0]->null_value))
-    return (String*) 0;
+    goto null_date;
+
+  ltime.neg= 0;
   if (seconds < 0)
   {
     seconds= -seconds;
-    sign= "-";
+    ltime.neg= 1;
   }
-  uint sec= (uint) ((ulonglong) seconds % 3600);
-  length= my_sprintf(buff,(buff,"%s%02lu:%02u:%02u",sign,(long) (seconds/3600),
-			   sec/60, sec % 60));
-  str->copy(buff, length, &my_charset_latin1, default_charset());
-  return str;
+
+  sec= (uint) ((ulonglong) seconds % 3600);
+  ltime.day= 0;
+  ltime.hour= seconds/3600;
+  ltime.minute= sec/60;
+  ltime.second= sec % 60;
+
+  tmp_format= &t_datetime_frm(thd, TIME_FORMAT_TYPE).datetime_format;
+  if (make_datetime(str, &ltime, 0, 0,
+		    tmp_format->format, tmp_format->format_length, 1))
+    return str;
+
+  null_value= 1;
+null_date:
+  return (String*) 0;
 }
 
 
@@ -879,9 +1359,7 @@ String *Item_func_date_format::val_str(String *str)
 {
   String *format;
   TIME l_time;
-  char intbuff[15];
   uint size,weekday;
-  ulong length;
 
   if (!date_or_time)
   {
@@ -892,24 +1370,17 @@ String *Item_func_date_format::val_str(String *str)
   {
     String *res;
     if (!(res=args[0]->val_str(str)))
-    {
-      null_value=1;
-      return 0;
-    }
-    if (str_to_time(res->ptr(),res->length(),&l_time))
-    {
-      null_value=1;
-      return 0;
-    }
+      goto null_date;
+
+    if (str_to_time(res->ptr(),res->length(),&l_time, thd))
+      goto null_date;
+
     l_time.year=l_time.month=l_time.day=0;
     null_value=0;
   }
 
   if (!(format = args[1]->val_str(str)) || !format->length())
-  {
-    null_value=1;
-    return 0;
-  }
+    goto null_date;
 
   if (fixed_length)
     size=max_length;
@@ -918,237 +1389,53 @@ String *Item_func_date_format::val_str(String *str)
   if (format == str)
     str= &value;				// Save result here
   if (str->alloc(size))
-  {
-    null_value=1;
-    return 0;
-  }
-  str->length(0);
+    goto null_date;
+
 
   /* Create the result string */
-  const char *ptr=format->ptr();
-  const char *end=ptr+format->length();
-  for (; ptr != end ; ptr++)
-  {
-    if (*ptr != '%' || ptr+1 == end)
-      str->append(*ptr);
-    else
-    {
-      switch (*++ptr) {
-      case 'M':
-	if (!l_time.month)
-	{
-	  null_value=1;
-	  return 0;
-	}
-	str->append(month_names[l_time.month-1]);
-	break;
-      case 'b':
-	if (!l_time.month)
-	{
-	  null_value=1;
-	  return 0;
-	}
-	str->append(month_names[l_time.month-1].ptr(),3);
-	break;
-      case 'W':
-	if (date_or_time)
-	{
-	  null_value=1;
-	  return 0;
-	}
-	weekday=calc_weekday(calc_daynr(l_time.year,l_time.month,l_time.day),0);
-	str->append(day_names[weekday]);
-	break;
-      case 'a':
-	if (date_or_time)
-	{
-	  null_value=1;
-	  return 0;
-	}
-	weekday=calc_weekday(calc_daynr(l_time.year,l_time.month,l_time.day),0);
-	str->append(day_names[weekday].ptr(),3);
-	break;
-      case 'D':
-	if (date_or_time)
-	{
-	  null_value=1;
-	  return 0;
-	}
-	length= int10_to_str(l_time.day, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 1, '0');
-	if (l_time.day >= 10 &&  l_time.day <= 19)
-	  str->append("th");
-	else
-	{
-	  switch (l_time.day %10) {
-	  case 1:
-	    str->append("st",2);
-	    break;
-	  case 2:
-	    str->append("nd",2);
-	    break;
-	  case 3:
-	    str->append("rd",2);
-	    break;
-	  default:
-	    str->append("th",2);
-	    break;
-	  }
-	}
-	break;
-      case 'Y':
-	length= int10_to_str(l_time.year, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 4, '0');
-	break;
-      case 'y':
-	length= int10_to_str(l_time.year%100, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-	break;
-      case 'm':
-	length= int10_to_str(l_time.month, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-	break;
-      case 'c':
-	length= int10_to_str(l_time.month, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 1, '0');
-	break;
-      case 'd':
-	length= int10_to_str(l_time.day, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-	break;
-      case 'e':
-	length= int10_to_str(l_time.day, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 1, '0');
-	break;
-      case 'f':
-	length= int10_to_str(l_time.second_part, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 6, '0');
-	break;
-      case 'H':
-	length= int10_to_str(l_time.hour, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-	break;
-      case 'h':
-      case 'I':
-	length= int10_to_str((l_time.hour+11)%12+1, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-	break;
-      case 'i':					/* minutes */
-	length= int10_to_str(l_time.minute, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-	break;
-      case 'j':
-	if (date_or_time)
-	{
-	  null_value=1;
-	  return 0;
-	}
-	length= int10_to_str(calc_daynr(l_time.year,l_time.month,l_time.day) - 
-		     calc_daynr(l_time.year,1,1) + 1, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 3, '0');
-	break;
-      case 'k':
-	length= int10_to_str(l_time.hour, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 1, '0');
-	break;
-      case 'l':
-	length= int10_to_str((l_time.hour+11)%12+1, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 1, '0');
-	break;
-      case 'p':
-	str->append(l_time.hour < 12 ? "AM" : "PM",2);
-	break;
-      case 'r':
-	length= my_sprintf(intbuff, 
-		   (intbuff, 
-		    (l_time.hour < 12) ? "%02d:%02d:%02d AM" : "%02d:%02d:%02d PM",
-		    (l_time.hour+11)%12+1,
-		    l_time.minute,
-		    l_time.second));
-	str->append(intbuff, length);
-	break;
-      case 'S':
-      case 's':
-	length= int10_to_str(l_time.second, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-	break;
-      case 'T':
-	length= my_sprintf(intbuff, 
-		   (intbuff, 
-		    "%02d:%02d:%02d", 
-		    l_time.hour, 
-		    l_time.minute,
-		    l_time.second));
-	str->append(intbuff, length);
-	break;
-      case 'U':
-      case 'u':
-      {
-	uint year;
-	length= int10_to_str(calc_week(&l_time, 0, (*ptr) == 'U', &year),
-		     intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-      }
-      break;
-      case 'v':
-      case 'V':
-      {
-	uint year;
-	length= int10_to_str(calc_week(&l_time, 1, (*ptr) == 'V', &year),
-		     intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 2, '0');
-      }
-      break;
-      case 'x':
-      case 'X':
-      {
-	uint year;
-	(void) calc_week(&l_time, 1, (*ptr) == 'X', &year);
-	length= int10_to_str(year, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 4, '0');
-      }
-      break;
-      case 'w':
-	weekday=calc_weekday(calc_daynr(l_time.year,l_time.month,l_time.day),1);
-	length= int10_to_str(weekday, intbuff, 10) - intbuff;
-	str->append_with_prefill(intbuff, length, 1, '0');
+  if (make_datetime(str, &l_time, 0, 0,
+		    format->ptr(), format->length(), 1))
+    return str;
 
-	break;
-      default:
-	str->append(*ptr);
-	break;
-      }
-    }
-  }
-  return str;
+null_date:
+  null_value=1;
+  return 0;
 }
 
 
 String *Item_func_from_unixtime::val_str(String *str)
 {
   struct tm tm_tmp,*start;
+  DATETIME_FORMAT *tmp_format;
   time_t tmp=(time_t) args[0]->val_int();
   uint32 l;
   CHARSET_INFO *cs=default_charset();
+  TIME ltime;
   
   if ((null_value=args[0]->null_value))
-    return 0;
+    goto null_date;
+
   localtime_r(&tmp,&tm_tmp);
   start=&tm_tmp;
-  
+
+  ltime.year= start->tm_year+1900;
+  ltime.month= start->tm_mon+1;
+  ltime.day= start->tm_mday;
+  ltime.hour= start->tm_hour;
+  ltime.minute= start->tm_min;
+  ltime.second= start->tm_sec;
+  ltime.second_part= 0;
+  ltime.neg=0;
+
   l=20*cs->mbmaxlen+32;
-  if (str->alloc(l))
-    return str;					/* purecov: inspected */
-  l=cs->cset->snprintf(cs,(char*) str->ptr(),l,"%04d-%02d-%02d %02d:%02d:%02d",
-	  (int) start->tm_year+1900,
-	  (int) start->tm_mon+1,
-	  (int) start->tm_mday,
-	  (int) start->tm_hour,
-	  (int) start->tm_min,
-	  (int) start->tm_sec);
-  str->length(l);
-  str->set_charset(cs);
-  return str;
+  tmp_format= &t_datetime_frm(thd, DATETIME_FORMAT_TYPE).datetime_format;
+  if (str->alloc(l) && make_datetime(str, &ltime, 1, 0,
+				     tmp_format->format,
+				     tmp_format->format_length, 1))
+    return str;
+  null_value= 1;
+null_date:
+  return 0;
 }
 
 
@@ -1229,7 +1516,7 @@ bool Item_date_add_interval::get_date(TIME *ltime, bool fuzzy_date)
 {
   long period,sign;
   INTERVAL interval;
-
+  ltime->neg= 0;
   if (args[0]->get_date(ltime,0) ||
       get_interval_value(args[1],int_type,&value,&interval))
     goto null_date;
@@ -1329,19 +1616,17 @@ bool Item_date_add_interval::get_date(TIME *ltime, bool fuzzy_date)
 String *Item_date_add_interval::val_str(String *str)
 {
   TIME ltime;
-  enum date_time_format_types format;
+  DATETIME_FORMAT *tmp_format;
 
   if (Item_date_add_interval::get_date(&ltime,0))
     return 0;
 
   if (ltime.time_type == TIMESTAMP_DATE)
-    format= DATE_ONLY;
-  else if (ltime.second_part)
-    format= DATE_TIME_MICROSECOND;
-  else
-    format= DATE_TIME;
-
-  if (make_datetime(str, &ltime, format))
+    tmp_format= &t_datetime_frm(thd, DATE_FORMAT_TYPE).datetime_format;
+  else 
+    tmp_format= &t_datetime_frm(thd, DATETIME_FORMAT_TYPE).datetime_format;
+  if (make_datetime(str, &ltime, 1, ltime.second_part,
+		    tmp_format->format, tmp_format->format_length, 1))
     return str;
 
   null_value=1;
@@ -1400,7 +1685,7 @@ longlong Item_extract::val_int()
   else
   {
     String *res= args[0]->val_str(&value);
-    if (!res || str_to_time(res->ptr(),res->length(),&ltime))
+    if (!res || str_to_time(res->ptr(),res->length(),&ltime, thd))
     {
       null_value=1;
       return 0;
@@ -1408,7 +1693,6 @@ longlong Item_extract::val_int()
     neg= ltime.neg ? -1 : 1;
     null_value=0;
   }
-
   switch (int_type) {
   case INTERVAL_YEAR:		return ltime.year;
   case INTERVAL_YEAR_MONTH:	return ltime.year*100L+ltime.month;
@@ -1530,10 +1814,12 @@ void Item_char_typecast::fix_length_and_dec()
 String *Item_datetime_typecast::val_str(String *str)
 {
   TIME ltime;
+  DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				(thd, DATETIME_FORMAT_TYPE).datetime_format);
 
   if (!get_arg0_date(&ltime,1) &&
-      make_datetime(str, &ltime, ltime.second_part ?
-	    DATE_TIME_MICROSECOND : DATE_TIME))
+      make_datetime(str, &ltime, 1, ltime.second_part,
+		    tmp_format->format, tmp_format->format_length, 1))
   return str;
 
 null_date:
@@ -1553,9 +1839,12 @@ bool Item_time_typecast::get_time(TIME *ltime)
 String *Item_time_typecast::val_str(String *str)
 {
   TIME ltime;
+  DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				(thd, TIME_FORMAT_TYPE).datetime_format);
 
   if (!get_arg0_time(&ltime) &&
-      make_datetime(str, &ltime, ltime.second_part ? TIME_MICROSECOND : TIME_ONLY))
+      make_datetime(str, &ltime, 0, ltime.second_part,
+		    tmp_format->format, tmp_format->format_length, 1))
     return str;
 
   null_value=1;
@@ -1574,9 +1863,12 @@ bool Item_date_typecast::get_date(TIME *ltime, bool fuzzy_date)
 String *Item_date_typecast::val_str(String *str)
 {
   TIME ltime;
+  DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				(thd, DATE_FORMAT_TYPE).datetime_format);
 
   if (!get_arg0_date(&ltime,1) &&
-      make_datetime(str, &ltime, DATE_ONLY))
+      make_datetime(str, &ltime, 1, 0,
+		    tmp_format->format, tmp_format->format_length, 1))
   return str;
 
 null_date:
@@ -1605,7 +1897,11 @@ String *Item_func_makedate::val_str(String *str)
   {
     null_value=0;
     get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day);
-    if (make_datetime(str, &l_time, DATE_ONLY))
+
+    DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				  (thd, DATE_FORMAT_TYPE).datetime_format);
+    if (make_datetime(str, &l_time, 1, 0,
+		      tmp_format->format, tmp_format->format_length, 1))
       return str;
   }
 
@@ -1656,6 +1952,7 @@ String *Item_func_add_time::val_str(String *str)
   bool is_time= 0;
   long microseconds, seconds, days= 0;
   int l_sign= sign;
+  DATETIME_FORMAT *tmp_format;
 
   null_value=0;
   l_time3.neg= 0;
@@ -1720,19 +2017,21 @@ String *Item_func_add_time::val_str(String *str)
   calc_time_from_sec(&l_time3, seconds, microseconds);
   if (!is_time)
   {
+    tmp_format= &t_datetime_frm(thd, DATETIME_FORMAT_TYPE).datetime_format;
     get_date_from_daynr(days,&l_time3.year,&l_time3.month,&l_time3.day);
     if (l_time3.day &&
-	make_datetime(str, &l_time3,
-	              l_time1.second_part || l_time2.second_part ?
-	              DATE_TIME_MICROSECOND : DATE_TIME))
+	make_datetime(str, &l_time3, 1,
+		      l_time1.second_part || l_time2.second_part,
+		      tmp_format->format, tmp_format->format_length, 1))
       return str;
     goto null_date;
   }
-
+  
+  tmp_format= &t_datetime_frm(thd, TIME_FORMAT_TYPE).datetime_format;
   l_time3.hour+= days*24;
-  if (make_datetime(str, &l_time3,
-	    l_time1.second_part || l_time2.second_part ?
-	    TIME_MICROSECOND : TIME_ONLY))
+  if (make_datetime(str, &l_time3, 0,
+		    l_time1.second_part || l_time2.second_part,
+		    tmp_format->format, tmp_format->format_length, 1))
     return str;
 
 null_date:
@@ -1755,6 +2054,7 @@ String *Item_func_timediff::val_str(String *str)
   long days;
   int l_sign= 1;
   TIME l_time1 ,l_time2, l_time3;
+  DATETIME_FORMAT *tmp_format;
 
   null_value= 0;  
   if (args[0]->get_time(&l_time1) ||
@@ -1800,9 +2100,11 @@ String *Item_func_timediff::val_str(String *str)
     l_time3.neg= l_time3.neg ? 0 : 1;
 
   calc_time_from_sec(&l_time3, seconds, microseconds);
-  if (make_datetime(str, &l_time3, 
-	    l_time1.second_part || l_time2.second_part ?
-	    TIME_MICROSECOND : TIME_ONLY))
+
+  tmp_format= &t_datetime_frm(thd, TIME_FORMAT_TYPE).datetime_format;
+  if (make_datetime(str, &l_time3, 0,
+		    l_time1.second_part || l_time2.second_part,
+		    tmp_format->format, tmp_format->format_length, 1))
     return str;
 
 null_date:
@@ -1819,6 +2121,7 @@ null_date:
 String *Item_func_maketime::val_str(String *str)
 {
   TIME ltime;
+  DATETIME_FORMAT *tmp_format;
 
   long hour= args[0]->val_int();
   long minute= args[1]->val_int();
@@ -1840,7 +2143,9 @@ String *Item_func_maketime::val_str(String *str)
   ltime.hour= (ulong)hour;
   ltime.minute= (ulong)minute;
   ltime.second= (ulong)second;
-  if (make_datetime(str, &ltime, TIME_ONLY))
+  tmp_format= &t_datetime_frm(thd, TIME_FORMAT_TYPE).datetime_format;
+  if (make_datetime(str, &ltime, 0, 0,
+		    tmp_format->format, tmp_format->format_length, 1))
     return str;
 
 null_date:
@@ -1860,3 +2165,79 @@ longlong Item_func_microsecond::val_int()
     return ltime.second_part;
   return 0;
 }
+
+/*
+   Array of MySQL date/time/datetime formats
+   Firts element is date format
+   Second element is time format
+   Third element is datetime format
+   Fourth is format name.
+*/
+
+const char *datetime_formats[4][5]=
+{
+  {"%m.%d.%Y", "%Y-%m-%d", "%Y-%m-%d", "%d.%m.%Y", "%Y%m%d"},
+  {"%h:%i:%s %p", "%H:%i:%s", "%H:%i:%s", "%H.%i.%S", "%H%i%s"},
+  {"%Y-%m-%d-%H.%i.%s", "%Y-%m-%d %H:%i:%s", "%Y-%m-%d %H:%i:%s", "%Y-%m-%d-%H.%i.%s", "%Y%m%d%H%i%s"},
+  {"USA", "JIS", "ISO", "EUR", "INTERNAL"}
+};
+
+
+/*
+   Return format string according format name.
+   If name is unknown, result is ISO format string
+*/
+
+String *Item_func_get_format::val_str(String *str)
+{
+  String *val=args[0]->val_str(str);
+  const char *format_str= datetime_formats[tm_format][ISO_FORMAT]; 
+
+  if (!args[0]->null_value)
+  {
+    const char *val_ptr= val->ptr();
+    uint val_len= val->length();
+    for (int i= 0; i < 5; i++)
+    {
+      const char *name_format_str= datetime_formats[3][i];
+      uint format_str_len= strlen(name_format_str);
+      if ( val_len == format_str_len &&
+	   !my_strnncoll(&my_charset_latin1, 
+			 (const uchar *) val_ptr, val_len, 
+			 (const uchar *) name_format_str, format_str_len))
+      {
+	format_str= datetime_formats[tm_format][i];
+	break;
+      }	    
+    }
+  }
+
+  null_value= 0;
+  str->length(0);
+  str->append(format_str);
+  return str;
+}
+
+
+String *Item_func_str_to_date::val_str(String *str)
+{
+  TIME ltime;
+  bzero((char*) &ltime, sizeof(ltime));
+  DATETIME_FORMAT *tmp_format;
+  String *val=args[0]->val_str(str);
+  String *format=args[1]->val_str(str);
+  if (args[0]->null_value || args[1]->null_value ||
+      extract_datetime(val->ptr(), val->length(),
+		       format->ptr(), val->length(),
+		       &ltime))
+    goto null_date;
+
+  tmp_format= &t_datetime_frm(thd, DATETIME_FORMAT_TYPE).datetime_format;
+  if (make_datetime(str, &ltime, 0, 0, tmp_format->format,
+		    tmp_format->format_length, 1))
+    return str;
+
+null_date:
+  null_value=1;
+  return 0;
+}
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 6dcf7d00ce1..b7bf294b83d 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -763,3 +763,46 @@ public:
     maybe_null=1;
   }
 };
+
+
+enum datetime_format
+{
+  USA_FORMAT, JIS_FORMAT, ISO_FORMAT, EUR_FORMAT, INTERNAL_FORMAT
+};
+
+
+enum datetime_format_types
+{
+  DATE_FORMAT_TYPE= 0, TIME_FORMAT_TYPE, DATETIME_FORMAT_TYPE
+};
+
+
+class Item_func_get_format :public Item_str_func
+{
+  const datetime_format_types tm_format;
+public:
+  Item_func_get_format(datetime_format_types type_arg1, Item *a)
+    :Item_str_func(a), tm_format(type_arg1) {}
+  String *val_str(String *str);
+  const char *func_name() const { return "get_format"; }
+  void fix_length_and_dec()
+  {
+    decimals=0;
+    max_length=17*MY_CHARSET_BIN_MB_MAXLEN;
+  }
+};
+
+
+class Item_func_str_to_date :public Item_str_func
+{
+public:
+  Item_func_str_to_date(Item *a, Item *b)
+    :Item_str_func(a, b) {}
+  String *val_str(String *str);
+  const char *func_name() const { return "str_to_date"; }
+  void fix_length_and_dec()
+  {
+    decimals=0;
+    max_length=29*MY_CHARSET_BIN_MB_MAXLEN;
+  }
+};
diff --git a/sql/lex.h b/sql/lex.h
index f8bb34c5c5b..5d79e378d4f 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -177,6 +177,7 @@ static SYMBOL symbols[] = {
   { "FUNCTION",		SYM(UDF_SYM),0,0},
   { "GEOMETRY",		SYM(GEOMETRY_SYM),0,0},
   { "GEOMETRYCOLLECTION",SYM(GEOMETRYCOLLECTION),0,0},
+  { "GET_FORMAT",       SYM(GET_FORMAT),0,0},
   { "GLOBAL",		SYM(GLOBAL_SYM),0,0},
   { "GRANT",		SYM(GRANT),0,0},
   { "GRANTS",	        SYM(GRANTS),0,0},
@@ -640,6 +641,7 @@ static SYMBOL sql_functions[] = {
   { "STARTPOINT",	SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_startpoint)},
   { "STD",		SYM(STD_SYM),0,0},
   { "STDDEV",		SYM(STD_SYM),0,0},
+  { "STR_TO_DATE",	SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_str_to_date)},
   { "STRCMP",		SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_strcmp)},
   { "SUBSTR",	  	SYM(SUBSTRING),0,0},
   { "SUBSTRING",	SYM(SUBSTRING),0,0},
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 6c1f7bfa472..c8d793ede29 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -822,7 +822,7 @@ extern pthread_mutex_t LOCK_mysql_create_db,LOCK_Acl,LOCK_open,
        LOCK_delayed_status, LOCK_delayed_create, LOCK_crypt, LOCK_timezone,
        LOCK_slave_list, LOCK_active_mi, LOCK_manager,
        LOCK_global_system_variables, LOCK_user_conn;
-extern rw_lock_t	LOCK_grant;
+extern rw_lock_t      LOCK_grant;
 extern pthread_cond_t COND_refresh, COND_thread_count, COND_manager;
 extern pthread_attr_t connection_attrib;
 extern I_List<THD> threads;
@@ -839,6 +839,14 @@ extern SHOW_COMP_OPTION have_berkeley_db;
 extern struct system_variables global_system_variables;
 extern struct system_variables max_system_variables;
 extern struct rand_struct sql_rand;
+
+#define g_datetime_frm(a) (global_system_variables.datetime_formats[(a)])
+#define t_datetime_frm(a, b) ((a)->variables.datetime_formats[(b)])
+
+extern const char *datetime_formats[4][5];
+extern const char *opt_datetime_format_names[3];
+extern const char *opt_datetime_formats[3];
+
 extern String null_string;
 extern HASH open_cache;
 extern TABLE *unused_tables;
@@ -909,14 +917,24 @@ void get_date_from_daynr(long daynr,uint *year, uint *month,
 			 uint *day);
 void init_time(void);
 long my_gmt_sec(TIME *, long *current_timezone);
-time_t str_to_timestamp(const char *str,uint length);
-bool str_to_time(const char *str,uint length,TIME *l_time);
-longlong str_to_datetime(const char *str,uint length,bool fuzzy_date);
+time_t str_to_timestamp(const char *str,uint length, THD *thd);
+bool str_to_time(const char *str,uint length,TIME *l_time, THD *thd);
+longlong str_to_datetime(const char *str,uint length,bool fuzzy_date, THD *thd);
 timestamp_type str_to_TIME(const char *str, uint length, TIME *l_time,
-			   bool fuzzy_date);
+			   bool fuzzy_date, THD *thd);
 void localtime_to_TIME(TIME *to, struct tm *from);
 void calc_time_from_sec(TIME *to, long seconds, long microseconds);
 
+extern DATETIME_FORMAT *make_format(DATETIME_FORMAT *datetime_format,
+				    datetime_format_types format_type,
+				    const char *format_str, 
+				    uint format_length, bool is_alloc);
+extern String *make_datetime(String *str, TIME *l_time,
+			     const bool is_time_only,
+			     const bool add_second_frac,
+			     const char *ptr, uint format_length,
+			     bool set_len_to_zero);
+
 int test_if_number(char *str,int *res,bool allow_wildcards);
 void change_byte(byte *,uint,char,char);
 extern "C" void unireg_abort(int exit_code);
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 812a5b28ff7..8a8c3a6c32d 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -319,6 +319,12 @@ char* log_error_file_ptr= log_error_file;
 char mysql_real_data_home[FN_REFLEN],
      language[LIBLEN],reg_ext[FN_EXTLEN], mysql_charsets_dir[FN_REFLEN],
      max_sort_char,*mysqld_user,*mysqld_chroot, *opt_init_file;
+
+const char *opt_datetime_formats[3];
+const char *opt_datetime_format_names[3]= {"date_format",
+					   "time_format",
+					   "datetime_format"};
+
 char *language_ptr, *default_collation_name, *default_character_set_name;
 char mysql_data_home_buff[2], *mysql_data_home=mysql_real_data_home;
 char server_version[SERVER_VERSION_LENGTH]=MYSQL_SERVER_VERSION;
@@ -906,6 +912,9 @@ void clean_up(bool print_message)
 #ifdef USE_RAID
   end_raid();
 #endif
+  g_datetime_frm(DATE_FORMAT_TYPE).clean();
+  g_datetime_frm(TIME_FORMAT_TYPE).clean();
+  g_datetime_frm(DATETIME_FORMAT_TYPE).clean();
   if (defaults_argv)
     free_defaults(defaults_argv);
   free_tmpdir(&mysql_tmpdir_list);
@@ -1998,6 +2007,36 @@ bool open_log(MYSQL_LOG *log, const char *hostname,
 }
 
 
+int init_global_datetime_format(datetime_format_types format_type, bool is_alloc)
+{
+  const char *format_str= opt_datetime_formats[format_type];
+  uint format_length= 0;
+  DATETIME_FORMAT *tmp_format= &g_datetime_frm(format_type).datetime_format;
+
+  if (format_str)
+  {
+    format_str= opt_datetime_formats[format_type];
+    format_length= strlen(format_str);
+  }
+  else
+  {
+    format_str= datetime_formats[format_type][ISO_FORMAT];
+    format_length= strlen(datetime_formats[format_type][ISO_FORMAT]);
+    opt_datetime_formats[format_type]= format_str;
+  }
+  if (make_format(tmp_format, format_type, format_str,
+		  format_length, is_alloc))
+  {
+    g_datetime_frm(format_type).name= opt_datetime_format_names[format_type];
+    g_datetime_frm(format_type).name_length=
+      strlen(opt_datetime_format_names[format_type]);
+    g_datetime_frm(format_type).format_type= format_type;
+    return 0;
+  }
+  return 1;
+}
+
+
 static int init_common_variables(const char *conf_file_name, int argc,
 				 char **argv, const char **groups)
 {
@@ -2113,6 +2152,12 @@ static int init_common_variables(const char *conf_file_name, int argc,
   global_system_variables.collation_connection= default_charset_info;
   global_system_variables.character_set_results= default_charset_info;
   global_system_variables.character_set_client= default_charset_info;
+  global_system_variables.collation_connection= default_charset_info;
+
+  if (init_global_datetime_format(DATE_FORMAT_TYPE, 1) ||
+      init_global_datetime_format(TIME_FORMAT_TYPE, 1) ||
+      init_global_datetime_format(DATETIME_FORMAT_TYPE, 1))
+    return 1;
 
   if (use_temp_pool && bitmap_init(&temp_pool,1024,1))
     return 1;
@@ -3578,6 +3623,9 @@ enum options_mysqld
   OPT_GROUP_CONCAT_MAX_LEN,
   OPT_DEFAULT_COLLATION,
   OPT_SECURE_AUTH,
+  OPT_DATE_FORMAT,
+  OPT_TIME_FORMAT,
+  OPT_DATETIME_FORMAT,
   OPT_LOG_QUERIES_NOT_USING_INDEXES
 };
 
@@ -4530,6 +4578,21 @@ The minimum value for this variable is 4096.",
     (gptr*) &global_system_variables.default_week_format,
     (gptr*) &max_system_variables.default_week_format,
     0, GET_ULONG, REQUIRED_ARG, 0, 0, 3L, 0, 1, 0},
+  { "date-format", OPT_DATE_FORMAT,
+    "The DATE format.",
+    (gptr*) &opt_datetime_formats[DATE_FORMAT_TYPE],
+    (gptr*) &opt_datetime_formats[DATE_FORMAT_TYPE],
+    0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+  { "datetime-format", OPT_DATETIME_FORMAT,
+    "The DATETIME/TIMESTAMP format.",
+    (gptr*) &opt_datetime_formats[DATETIME_FORMAT_TYPE],
+    (gptr*) &opt_datetime_formats[DATETIME_FORMAT_TYPE],
+    0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+  { "time-format", OPT_TIME_FORMAT,
+    "The TIME format.",
+    (gptr*) &opt_datetime_formats[TIME_FORMAT_TYPE],
+    (gptr*) &opt_datetime_formats[TIME_FORMAT_TYPE],
+    0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
   {0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
 };
 
@@ -4907,6 +4970,10 @@ static void mysql_init_variables(void)
   max_system_variables.max_join_size=   (ulonglong) HA_POS_ERROR;
   global_system_variables.old_passwords= 0;
 
+  init_global_datetime_format(DATE_FORMAT_TYPE, 0);
+  init_global_datetime_format(TIME_FORMAT_TYPE, 0);
+  init_global_datetime_format(DATETIME_FORMAT_TYPE, 0);
+
   /* Variables that depends on compile options */
 #ifndef DBUG_OFF
   default_dbug_option=IF_WIN("d:t:i:O,\\mysqld.trace",
diff --git a/sql/protocol.cc b/sql/protocol.cc
index 79420fb71d5..0fe759cff67 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -833,17 +833,12 @@ bool Protocol_simple::store(TIME *tm)
   field_pos++;
 #endif
   char buff[40];
-  uint length;
-  length= my_sprintf(buff,(buff, "%04d-%02d-%02d %02d:%02d:%02d",
-			   (int) tm->year,
-			   (int) tm->month,
-			   (int) tm->day,
-			   (int) tm->hour,
-			   (int) tm->minute,
-			   (int) tm->second));
-  if (tm->second_part)
-    length+= my_sprintf(buff+length,(buff+length, ".%06d", (int)tm->second_part));
-  return net_store_data((char*) buff, length);
+  String tmp((char*) buff,sizeof(buff),&my_charset_bin);
+  DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				(current_thd, DATETIME_FORMAT_TYPE).datetime_format);
+  make_datetime(&tmp, tm, 1, tm->second_part,
+		tmp_format->format, tmp_format->format_length, 1);
+  return net_store_data((char*) tmp.ptr(), tmp.length());
 }
 
 
@@ -855,12 +850,12 @@ bool Protocol_simple::store_date(TIME *tm)
   field_pos++;
 #endif
   char buff[40];
-  uint length;
-  length= my_sprintf(buff,(buff, "%04d-%02d-%02d",
-			   (int) tm->year,
-			   (int) tm->month,
-			   (int) tm->day));
-  return net_store_data((char*) buff, length);
+  String tmp((char*) buff,sizeof(buff),&my_charset_bin);
+  DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				 (current_thd, DATE_FORMAT_TYPE).datetime_format);
+  make_datetime(&tmp, tm, 1, 0,
+		tmp_format->format, tmp_format->format_length, 1);
+  return net_store_data((char*) tmp.ptr(), tmp.length());
 }
 
 
@@ -878,16 +873,14 @@ bool Protocol_simple::store_time(TIME *tm)
   field_pos++;
 #endif
   char buff[40];
-  uint length;
+  String tmp((char*) buff,sizeof(buff),&my_charset_bin);
+  DATETIME_FORMAT *tmp_format= (&t_datetime_frm
+				(current_thd, TIME_FORMAT_TYPE).datetime_format);
   uint day= (tm->year || tm->month) ? 0 : tm->day;
-  length= my_sprintf(buff,(buff, "%s%02ld:%02d:%02d",
-			   tm->neg ? "-" : "",
-			   (long) day*24L+(long) tm->hour,
-			   (int) tm->minute,
-			   (int) tm->second));
-  if (tm->second_part)
-    length+= my_sprintf(buff+length,(buff+length, ".%06d", (int)tm->second_part));
-  return net_store_data((char*) buff, length);
+  tm->hour= (long) day*24L+(long) tm->hour;
+  make_datetime(&tmp, tm, 0, tm->second_part,
+		tmp_format->format, tmp_format->format_length, 1);
+  return net_store_data((char*) tmp.ptr(), tmp.length());
 }
 
 
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 582010a4602..48580960399 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -403,6 +403,9 @@ sys_var *sys_variables[]=
   &sys_collation_server,
   &sys_concurrent_insert,
   &sys_connect_timeout,
+  &g_datetime_frm(DATE_FORMAT_TYPE),
+  &g_datetime_frm(DATETIME_FORMAT_TYPE),
+  &g_datetime_frm(TIME_FORMAT_TYPE),
   &sys_default_week_format,
   &sys_delay_key_write,
   &sys_delayed_insert_limit,
@@ -540,6 +543,8 @@ struct show_var_st init_vars[]= {
   {sys_concurrent_insert.name,(char*) &sys_concurrent_insert,       SHOW_SYS},
   {sys_connect_timeout.name,  (char*) &sys_connect_timeout,         SHOW_SYS},
   {"datadir",                 mysql_real_data_home,                 SHOW_CHAR},
+  {"date_format",             (char*) &g_datetime_frm(DATE_FORMAT_TYPE), SHOW_SYS},
+  {"datetime_format",         (char*) &g_datetime_frm(DATETIME_FORMAT_TYPE), SHOW_SYS},
   {"default_week_format",     (char*) &sys_default_week_format,     SHOW_SYS},
   {sys_delay_key_write.name,  (char*) &sys_delay_key_write,         SHOW_SYS},
   {sys_delayed_insert_limit.name, (char*) &sys_delayed_insert_limit,SHOW_SYS},
@@ -696,6 +701,7 @@ struct show_var_st init_vars[]= {
 #endif
   {"thread_stack",            (char*) &thread_stack,                SHOW_LONG},
   {sys_tx_isolation.name,     (char*) &sys_tx_isolation,	    SHOW_SYS},
+  {"time_format",             (char*) &g_datetime_frm(TIME_FORMAT_TYPE), SHOW_SYS},
 #ifdef HAVE_TZNAME
   {"timezone",                time_zone,                            SHOW_CHAR},
 #endif
@@ -709,9 +715,75 @@ struct show_var_st init_vars[]= {
   {NullS, NullS, SHOW_LONG}
 };
 
+
 /*
   Functions to check and update variables
 */
+char *update_datetime_format(THD *thd, enum enum_var_type type,
+			     enum datetime_format_types format_type,
+			     DATETIME_FORMAT *tmp_format)
+{
+  char *old_value;
+  if (type == OPT_GLOBAL)
+  {
+    pthread_mutex_lock(&LOCK_global_system_variables);    
+    old_value= g_datetime_frm(format_type).datetime_format.format;
+    g_datetime_frm(format_type).datetime_format= *tmp_format;
+    pthread_mutex_unlock(&LOCK_global_system_variables);
+  }
+  else
+  {
+    old_value= t_datetime_frm(thd,format_type).datetime_format.format;
+    t_datetime_frm(thd, format_type).datetime_format= *tmp_format;
+  }
+  return old_value;
+}
+
+
+bool sys_var_datetime_format::update(THD *thd, set_var *var)
+{
+  DATETIME_FORMAT tmp_format;
+  char *old_value;
+  uint new_length;
+
+  if ((new_length= var->value->str_value.length()))
+  {
+    if (!make_format(&tmp_format, format_type,
+		     var->value->str_value.ptr(),
+		     new_length, 1))
+    return 1;
+  }
+
+  old_value= update_datetime_format(thd, var->type, format_type, &tmp_format);
+  my_free(old_value, MYF(MY_ALLOW_ZERO_PTR));
+  return 0;
+}
+
+byte *sys_var_datetime_format::value_ptr(THD *thd, enum_var_type type,
+				       LEX_STRING *base)
+{
+ if (type == OPT_GLOBAL)
+   return (byte*) g_datetime_frm(format_type).datetime_format.format;
+ return (byte*) t_datetime_frm(thd, format_type).datetime_format.format;
+}
+
+void sys_var_datetime_format::set_default(THD *thd, enum_var_type type)
+{
+  DATETIME_FORMAT tmp_format;
+  char *old_value;
+  uint new_length;
+
+  if ((new_length= strlen(opt_datetime_formats[format_type])))
+  {
+    if (!make_format(&tmp_format, format_type,
+		     opt_datetime_formats[format_type],
+		     new_length, 1))
+    return;
+  }
+
+  old_value= update_datetime_format(thd, type, format_type, &tmp_format);
+  my_free(old_value, MYF(MY_ALLOW_ZERO_PTR));
+}
 
 /*
   The following 3 functions need to be changed in 4.1 when we allow
diff --git a/sql/set_var.h b/sql/set_var.h
index 70667c719e5..4c67c5ccc59 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -49,6 +49,8 @@ public:
   const char *name;
   
   sys_after_update_func after_update;
+  sys_var()
+  {}
   sys_var(const char *name_arg) :name(name_arg),after_update(0)
   {}
   sys_var(const char *name_arg,sys_after_update_func func)
@@ -188,6 +190,9 @@ public:
 class sys_var_thd :public sys_var
 {
 public:
+  sys_var_thd()
+    :sys_var()
+  {}
   sys_var_thd(const char *name_arg)
     :sys_var(name_arg)
   {}
@@ -555,6 +560,51 @@ public:
 };
 
 
+class sys_var_datetime_format :public sys_var_thd
+{
+public:
+  enum datetime_format_types format_type;
+  DATETIME_FORMAT datetime_format;
+  sys_var_datetime_format(): sys_var_thd()
+  {}
+
+  void clean()
+  {
+    my_free(datetime_format.format, MYF(MY_ALLOW_ZERO_PTR));
+    datetime_format.format=0;
+  }
+
+  /*
+    It's for copying of global_system_variables structure
+    in THD constructor.
+  */
+  inline sys_var_datetime_format& operator= (sys_var_datetime_format& s)
+  {
+    if (&s != this)
+    {
+      name= s.name; name_length= s.name_length;
+      datetime_format= s.datetime_format;
+      datetime_format.format= (my_strdup_with_length
+			       (s.datetime_format.format,
+				s.datetime_format.
+				format_length, MYF(0)));
+      format_type= s.format_type;
+    }
+    return *this;
+  }
+
+  SHOW_TYPE type() { return SHOW_CHAR; }
+  bool check_update_type(Item_result type)
+  {
+    return type != STRING_RESULT;		/* Only accept strings */
+  }
+  bool check_default(enum_var_type type) { return 0; }
+  bool update(THD *thd, set_var *var);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+  void set_default(THD *thd, enum_var_type type);
+};
+
+
 /* Variable that you can only read from */
 
 class sys_var_readonly: public sys_var
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index e240aed7e9f..588031643d4 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -279,6 +279,9 @@ void THD::cleanup(void)
     close_thread_tables(this);
   }
   close_temporary_tables(this);
+  variables.datetime_formats[DATE_FORMAT_TYPE].clean();
+  variables.datetime_formats[TIME_FORMAT_TYPE].clean();
+  variables.datetime_formats[DATETIME_FORMAT_TYPE].clean();
   delete_dynamic(&user_var_events);
   hash_free(&user_vars);
   if (global_read_lock)
diff --git a/sql/sql_class.h b/sql/sql_class.h
index f4687a9aa74..06bc29dbb2a 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -421,6 +421,7 @@ struct system_variables
   CHARSET_INFO	*collation_server;
   CHARSET_INFO	*collation_database;
   CHARSET_INFO  *collation_connection;
+  sys_var_datetime_format datetime_formats[3];
 };
 
 void free_tmp_table(THD *thd, TABLE *entry);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 7286be82706..8491ba7675d 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -78,6 +78,7 @@ inline Item *or_or_concat(THD *thd, Item* A, Item* B)
   CHARSET_INFO *charset;
   thr_lock_type lock_type;
   interval_type interval;
+  datetime_format_types datetime_format_type;
   st_select_lex *select_lex;
   chooser_compare_func_creator boolfunc2creator;
 }
@@ -482,6 +483,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token  GEOMETRYCOLLECTION
 %token  GROUP_CONCAT_SYM
 %token	GROUP_UNIQUE_USERS
+%token  GET_FORMAT
 %token	HOUR_MICROSECOND_SYM
 %token	HOUR_MINUTE_SYM
 %token	HOUR_SECOND_SYM
@@ -644,6 +646,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	UDF_CHAR_FUNC UDF_FLOAT_FUNC UDF_INT_FUNC
 	UDA_CHAR_SUM UDA_FLOAT_SUM UDA_INT_SUM
 
+%type <datetime_format_type> datetime_format_type;
 %type <interval> interval
 
 %type <db_type> table_types
@@ -2581,6 +2584,8 @@ simple_expr:
 	  { $$= new Item_func_spatial_collection(* $3,
                        Geometry::wkbGeometryCollection,
                        Geometry::wkbPoint); }
+	| GET_FORMAT '(' datetime_format_type  ',' expr ')'
+	  { $$= new Item_func_get_format($3, $5); }
 	| HOUR_SYM '(' expr ')'
 	  { $$= new Item_func_hour($3); }
 	| IF '(' expr ',' expr ',' expr ')'
@@ -3194,6 +3199,11 @@ interval:
 	| YEAR_MONTH_SYM	{ $$=INTERVAL_YEAR_MONTH; }
 	| YEAR_SYM		{ $$=INTERVAL_YEAR; };
 
+datetime_format_type:
+	DATE_SYM		{$$=DATE_FORMAT_TYPE;}
+	| TIME_SYM		{$$=TIME_FORMAT_TYPE;}
+	| DATETIME		{$$=DATETIME_FORMAT_TYPE;};
+
 table_alias:
 	/* empty */
 	| AS
@@ -4541,6 +4551,7 @@ keyword:
 	| FLUSH_SYM		{}
 	| GEOMETRY_SYM		{}
 	| GEOMETRYCOLLECTION	{}
+	| GET_FORMAT		{}
 	| GRANTS		{}
 	| GLOBAL_SYM		{}
 	| HANDLER_SYM		{}
diff --git a/sql/structs.h b/sql/structs.h
index 05ebdba7a37..d9be230c049 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -24,6 +24,11 @@ typedef struct st_date_format {		/* How to print date */
   uint pos[6];				/* Positions to YY.MM.DD HH:MM:SS */
 } DATE_FORMAT;
 
+typedef struct st_datetime_format {
+  byte dt_pos[8];
+  char *format;
+  uint format_length;
+} DATETIME_FORMAT;
 
 typedef struct st_keyfile_info {	/* used with ha_info() */
   byte ref[MAX_REFLENGTH];		/* Pointer to current row */
@@ -110,8 +115,8 @@ typedef struct st_read_record {			/* Parameter to read_record */
   bool print_error, ignore_not_found_rows;
 } READ_RECORD;
 
-enum timestamp_type { TIMESTAMP_NONE, TIMESTAMP_DATE, TIMESTAMP_FULL,
-		      TIMESTAMP_TIME };
+enum timestamp_type { TIMESTAMP_NONE, WRONG_TIMESTAMP_FULL, TIMESTAMP_DATE, TIMESTAMP_FULL,
+		      TIMESTAMP_TIME};
 
 typedef struct st_time {
   uint year,month,day,hour,minute,second;
diff --git a/sql/time.cc b/sql/time.cc
index 70ae8dcd8ed..f2e41afa560 100644
--- a/sql/time.cc
+++ b/sql/time.cc
@@ -27,6 +27,10 @@ uchar *days_in_month= (uchar*) "\037\034\037\036\037\036\037\037\036\037\036\037
 	/* Init some variabels needed when using my_local_time */
 	/* Currently only my_time_zone is inited */
 
+bool parse_datetime_formats(datetime_format_types format_type, 
+			    const char *format_str, uint format_length,
+			    byte *dt_pos);
+
 static long my_time_zone=0;
 
 void init_time(void)
@@ -316,10 +320,12 @@ ulong convert_month_to_period(ulong month)
 */
 
 timestamp_type
-str_to_TIME(const char *str, uint length, TIME *l_time,bool fuzzy_date)
+str_to_TIME(const char *str, uint length, TIME *l_time,bool fuzzy_date,THD *thd)
 {
-  uint field_length,year_length,digits,i,number_of_fields,date[7];
+  uint field_length= 0, year_length= 0, digits, i, number_of_fields;
+  uint date[7], date_len[7];
   uint not_zero_date;
+  bool is_internal_format= 0;
   const char *pos;
   const char *end=str+length;
   bool found_delimitier= 0;
@@ -336,24 +342,32 @@ str_to_TIME(const char *str, uint length, TIME *l_time,bool fuzzy_date)
     (YYYY-MM-DD,  YYYYMMDD, YYYYYMMDDHHMMSS)
   */
   for (pos=str; pos != end && my_isdigit(&my_charset_latin1,*pos) ; pos++) ;
+  /* Check for internal format */
   digits= (uint) (pos-str);
-  year_length= (digits == 4 || digits == 8 || digits >= 14) ? 4 : 2;
-  field_length=year_length-1;
+
+  if (pos == end || digits>=12)
+  {
+    is_internal_format= 1;
+    year_length= (digits == 4 || digits == 8 || digits >= 14) ? 4 : 2;
+    field_length=year_length-1;
+    date_len[0]= year_length;
+  }
   not_zero_date= 0;
   for (i=0 ; i < 6 && str != end && my_isdigit(&my_charset_latin1,*str) ; i++)
   {
+    if (!is_internal_format)
+      date_len[i]= 1;
     uint tmp_value=(uint) (uchar) (*str++ - '0');
-    while (str != end && my_isdigit(&my_charset_latin1,str[0]) &&
-	   field_length--)
+    while (str != end && my_isdigit(&my_charset_latin1,str[0]) 
+	   && (is_internal_format && field_length-- || !is_internal_format) )
     {
       tmp_value=tmp_value*10 + (uint) (uchar) (*str - '0');
       str++;
+      if (!is_internal_format)
+	date_len[i]+= 1;
     }
-    if (found_delimitier && (int) field_length < 0)
-    {
-      /* The number can't match any valid date or datetime string */
+    if (i == 2 && *str == '.')
       DBUG_RETURN(TIMESTAMP_NONE);
-    }
     date[i]=tmp_value;
     not_zero_date|= tmp_value;
     if (i == 2 && str != end && *str == 'T')
@@ -371,7 +385,8 @@ str_to_TIME(const char *str, uint length, TIME *l_time,bool fuzzy_date)
 	found_delimitier=1;			// Should be a 'normal' date
       }
     }
-    field_length=1;				// Rest fields can only be 2
+    if (is_internal_format)
+	field_length=1;				// Rest fields can only be 2
   }
   /* Handle second fractions */
   if (i == 6 && (uint) (end-str) >= 2 && *str == '.' && 
@@ -389,14 +404,69 @@ str_to_TIME(const char *str, uint length, TIME *l_time,bool fuzzy_date)
   else
     date[6]=0;
 
-  if (year_length == 2 && i >=2 && (date[1] || date[2]))
-    date[0]+= (date[0] < YY_PART_YEAR ? 2000 : 1900);
+  while (str != end && (my_ispunct(&my_charset_latin1,*str) || 
+			my_isspace(&my_charset_latin1,*str)))
+    str++;
+
+  uint add_hours= 0;
+  if (!my_strnncoll(&my_charset_latin1, 
+		    (const uchar *)str, 2, 
+		    (const uchar *)"PM", 2))
+    add_hours= 12;
+
   number_of_fields=i;
   while (i < 6)
     date[i++]=0;
-  if (number_of_fields < 3 || date[1] > 12 ||
-      date[2] > 31 || date[3] > 23 || date[4] > 59 || date[5] > 59 ||
-      (!fuzzy_date && (date[1] == 0 || date[2] == 0)))
+
+  if (!is_internal_format)
+  {
+    byte *frm_pos;
+
+    if (number_of_fields  <= 3)
+    {
+      frm_pos= t_datetime_frm(thd, DATE_FORMAT_TYPE).datetime_format.dt_pos;
+      l_time->hour=  0;
+      l_time->minute= 0;
+      l_time->second= 0;
+    }
+    else
+    {
+      frm_pos= t_datetime_frm(thd, DATETIME_FORMAT_TYPE).datetime_format.dt_pos;
+      l_time->hour=  date[(int) frm_pos[3]];
+      l_time->minute=date[(int) frm_pos[4]];
+      l_time->second=date[(int) frm_pos[5]];
+      if (frm_pos[6] == 1)
+      {
+	if (l_time->hour > 12)
+	  DBUG_RETURN(WRONG_TIMESTAMP_FULL);
+	l_time->hour= l_time->hour%12 + add_hours;
+      }
+    }
+
+    l_time->year=  date[(int) frm_pos[0]];
+    l_time->month= date[(int) frm_pos[1]];
+    l_time->day=  date[(int) frm_pos[2]];
+    year_length= date_len[(int) frm_pos[0]];
+  }
+  else
+  {
+    l_time->year=  date[0];
+    l_time->month= date[1];
+    l_time->day=  date[2];
+    l_time->hour=  date[3];
+    l_time->minute=date[4];
+    l_time->second=date[5];
+  }
+  l_time->second_part=date[6];
+  l_time->neg= 0;
+  if (year_length == 2 && i >=2 && (l_time->month || l_time->day))
+    l_time->year+= (l_time->year < YY_PART_YEAR ? 2000 : 1900);
+
+
+  if (number_of_fields < 3 || l_time->month > 12 ||
+      l_time->day > 31 || l_time->hour > 23 ||
+      l_time->minute > 59 || l_time->second > 59 ||
+      (!fuzzy_date && (l_time->month == 0 || l_time->day == 0)))
   {
     /* Only give warning for a zero date if there is some garbage after */
     if (!not_zero_date)				// If zero date
@@ -411,53 +481,46 @@ str_to_TIME(const char *str, uint length, TIME *l_time,bool fuzzy_date)
       }
     }
     if (not_zero_date)
-      current_thd->cuted_fields++;
-    DBUG_RETURN(TIMESTAMP_NONE);
+      thd->cuted_fields++;
+    DBUG_RETURN(WRONG_TIMESTAMP_FULL);
   }
-  if (str != end && current_thd->count_cuted_fields)
+  if (str != end && thd->count_cuted_fields)
   {
     for (; str != end ; str++)
     {
       if (!my_isspace(&my_charset_latin1,*str))
       {
-	current_thd->cuted_fields++;
+	thd->cuted_fields++;
 	break;
       }
     }
   }
-  l_time->year=  date[0];
-  l_time->month= date[1];
-  l_time->day=	 date[2];
-  l_time->hour=  date[3];
-  l_time->minute=date[4];
-  l_time->second=date[5];
-  l_time->second_part=date[6];
-  l_time->neg= 0;
+
   DBUG_RETURN(l_time->time_type=
 	      (number_of_fields <= 3 ? TIMESTAMP_DATE : TIMESTAMP_FULL));
 }
 
 
-time_t str_to_timestamp(const char *str,uint length)
+time_t str_to_timestamp(const char *str,uint length, THD *thd)
 {
   TIME l_time;
   long not_used;
 
-  if (str_to_TIME(str,length,&l_time,0) == TIMESTAMP_NONE)
+  if (str_to_TIME(str,length,&l_time,0,thd) <= WRONG_TIMESTAMP_FULL)
     return(0);
   if (l_time.year >= TIMESTAMP_MAX_YEAR || l_time.year < 1900+YY_PART_YEAR)
   {
-    current_thd->cuted_fields++;
+    thd->cuted_fields++;
     return(0);
   }
   return(my_gmt_sec(&l_time, &not_used));
 }
 
 
-longlong str_to_datetime(const char *str,uint length,bool fuzzy_date)
+longlong str_to_datetime(const char *str,uint length,bool fuzzy_date, THD *thd)
 {
   TIME l_time;
-  if (str_to_TIME(str,length,&l_time,fuzzy_date) == TIMESTAMP_NONE)
+  if (str_to_TIME(str,length,&l_time,fuzzy_date,thd) <= WRONG_TIMESTAMP_FULL)
     return(0);
   return (longlong) (l_time.year*LL(10000000000) +
 		     l_time.month*LL(100000000)+
@@ -484,12 +547,13 @@ longlong str_to_datetime(const char *str,uint length,bool fuzzy_date)
      1  error
 */
 
-bool str_to_time(const char *str,uint length,TIME *l_time)
+bool str_to_time(const char *str,uint length,TIME *l_time, THD *thd)
 {
   long date[5],value;
   const char *end=str+length;
   bool found_days,found_hours;
   uint state;
+  byte *frm_pos= t_datetime_frm(thd, TIME_FORMAT_TYPE).datetime_format.dt_pos;
 
   l_time->neg=0;
   for (; str != end && 
@@ -507,8 +571,11 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
   /* Check first if this is a full TIMESTAMP */
   if (length >= 12)
   {						// Probably full timestamp
-    if (str_to_TIME(str,length,l_time,1) == TIMESTAMP_FULL)
-      return 0;					// Was an ok timestamp
+    enum timestamp_type tres= str_to_TIME(str,length,l_time,1,thd);
+    if (tres == TIMESTAMP_FULL)
+      return 0;
+    else if (tres == WRONG_TIMESTAMP_FULL)
+      return 1;
   }
 
   /* Not a timestamp. Try to get this as a DAYS_TO_SECOND string */
@@ -533,7 +600,7 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
     found_days=1;
     str++;					// Skip space;
   }
-  else if ((end-str) > 1 && *str == ':' && 
+  else if ((end-str) > 1 &&  *str == frm_pos[7] &&
            my_isdigit(&my_charset_latin1,str[1]))
   {
     date[0]=0;					// Assume we found hours
@@ -559,8 +626,8 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
     for (value=0; str != end && my_isdigit(&my_charset_latin1,*str) ; str++)
       value=value*10L + (long) (*str - '0');
     date[state++]=value;
-    if (state == 4 || (end-str) < 2 || *str != ':' || 
-        !my_isdigit(&my_charset_latin1,str[1]))
+    if (state == 4 || (end-str) < 2 || *str != frm_pos[7] ||
+	!my_isdigit(&my_charset_latin1,str[1]))
       break;
     str++;					// Skip ':'
   }
@@ -577,7 +644,6 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
     else
       bzero((char*) (date+state), sizeof(long)*(4-state));
   }
-
  fractional:
   /* Get fractional second part */
   if ((end-str) >= 2 && *str == '.' && my_isdigit(&my_charset_latin1,str[1]))
@@ -593,6 +659,20 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
   else
     date[4]=0;
 
+  while (str != end && !my_isalpha(&my_charset_latin1,*str))
+    str++;
+
+  if ( (end-str)>= 2 &&
+       !my_strnncoll(&my_charset_latin1, 
+		     (const uchar *)str, 2, 
+		     (const uchar *)"PM", 2) &&
+       frm_pos[6] == 1)
+  {
+      uint days_i= date[1]/24;
+      uint hours_i= date[1]%24;
+      date[1]= hours_i%12 + 12 + 24*days_i;
+  }
+
   /* Some simple checks */
   if (date[2] >= 60 || date[3] >= 60)
   {
@@ -601,9 +681,9 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
   }
   l_time->month=0;
   l_time->day=date[0];
-  l_time->hour=date[1];
-  l_time->minute=date[2];
-  l_time->second=date[3];
+  l_time->hour=date[frm_pos[3] + 1];
+  l_time->minute=date[frm_pos[4] + 1];
+  l_time->second=date[frm_pos[5] + 1];
   l_time->second_part=date[4];
   l_time->time_type= TIMESTAMP_TIME;
 
@@ -648,3 +728,165 @@ void calc_time_from_sec(TIME *to, long seconds, long microseconds)
   to->second= t_seconds%60L;
   to->second_part= microseconds;
 }
+
+
+DATETIME_FORMAT *make_format(DATETIME_FORMAT *datetime_format,
+			     datetime_format_types format_type,
+			     const char *format_str,
+			     uint format_length, bool is_alloc)
+{
+  if (format_length &&
+      !parse_datetime_formats(format_type, format_str,
+			      format_length, 
+			      datetime_format->dt_pos))
+  {
+    if (is_alloc)
+    {
+      if (!(datetime_format->format= my_strdup_with_length(format_str,
+							   format_length,
+							   MYF(0))))
+	return 0;
+    }
+    else
+      datetime_format->format= (char *) format_str;
+    datetime_format->format_length= format_length;
+    return datetime_format;
+  }
+  return 0;
+}
+
+
+bool parse_datetime_formats(datetime_format_types format_type, 
+			    const char *format_str, uint format_length,
+			    byte *dt_pos)
+{
+  uint pos= 0;
+  dt_pos[0]= dt_pos[1]= dt_pos[2]= dt_pos[3]= 
+             dt_pos[4]= dt_pos[5]= dt_pos[6]= dt_pos[7]= -1;
+
+  const char *ptr=format_str;
+  const char *end=ptr+format_length;
+  bool need_p= 0;
+
+  for (; ptr != end; ptr++)
+  {
+    if (*ptr == '%' && ptr+1 != end)
+    {
+      switch (*++ptr) {
+      case 'y':
+      case 'Y':
+	if (dt_pos[0] > -1)
+	  return 1;
+	dt_pos[0]= pos;
+	break;
+      case 'c':
+      case 'm':
+	if (dt_pos[1] > -1)
+	  return 1;
+	dt_pos[1]= pos;
+	break;
+      case 'd':
+      case 'e':
+	if (dt_pos[2] > -1)
+	  return 1;
+	dt_pos[2]= pos;
+	break;
+      case 'H':
+      case 'k':
+      case 'h':
+      case 'I':
+      case 'l':
+	if (dt_pos[3] > -1)
+	  return 1;
+	dt_pos[3]= pos;
+	need_p= (*ptr == 'h' || *ptr == 'l' || *ptr == 'I');
+	break;
+      case 'i':
+	if (dt_pos[4] > -1)
+	  return 1;
+	dt_pos[4]= pos;
+	break;
+      case 's':
+      case 'S':
+	if (dt_pos[5] > -1)
+	  return 1;
+	dt_pos[5]= pos;
+	break;
+      case 'p':
+	if (dt_pos[6] > -1)
+	  return 1;
+	/* %p should be last in format string */
+	if (format_type == DATE_FORMAT_TYPE ||
+	    (pos != 6 && format_type == DATETIME_FORMAT_TYPE) ||
+	    (pos != 3 && format_type == TIME_FORMAT_TYPE))
+	  return 1;
+	dt_pos[6]= 1;
+	break;
+      default:
+	return 1;
+      }
+      if (dt_pos[6] == -1)
+	pos++;
+    }
+  }
+
+  if (pos > 5 && format_type == DATETIME_FORMAT_TYPE &&
+      (dt_pos[0] + dt_pos[1] + dt_pos[2] + 
+       dt_pos[3] + dt_pos[4] + dt_pos[5] != 15) ||
+      pos > 2 && format_type == DATE_FORMAT_TYPE && 
+      (dt_pos[0] + dt_pos[1] + dt_pos[2] != 3) ||
+      pos > 2 && format_type == TIME_FORMAT_TYPE &&
+      (dt_pos[3] + dt_pos[4] + dt_pos[5] != 3) ||
+      (need_p && dt_pos[6] != 1))
+      return 1;
+
+  /*
+    Check for valid separators between date/time parst
+  */
+  uint tmp_len= format_length;
+  if (dt_pos[6] == 1)
+  {
+    end= end - 2;
+    if (my_ispunct(&my_charset_latin1, *end) || my_isspace(&my_charset_latin1, *end))
+      end--;
+    tmp_len= end - format_str;
+  }
+  switch (format_type) {
+  case DATE_FORMAT_TYPE:
+  case TIME_FORMAT_TYPE:
+    if ((tmp_len == 6 && 
+	 !my_strnncoll(&my_charset_bin,
+		      (const uchar *) format_str, 6, 
+		      (const uchar *) datetime_formats
+		      [format_type][INTERNAL_FORMAT], 6)) ||
+	tmp_len == 8 &&
+	my_ispunct(&my_charset_latin1, *(format_str+2)) &&
+	my_ispunct(&my_charset_latin1, *(format_str+5)))
+    {
+      if (format_type == TIME_FORMAT_TYPE && tmp_len == 8)
+      {
+	if (*(format_str+2) != *(format_str+5))
+	  return 1;
+	dt_pos[7]= *(format_str+2);
+      }
+      return 0;
+    }
+    break;
+  case DATETIME_FORMAT_TYPE:
+    if ((tmp_len == 12 && 
+	 !my_strnncoll(&my_charset_bin, 
+		      (const uchar *) format_str, 12, 
+		      (const uchar *) datetime_formats
+		      [DATETIME_FORMAT_TYPE][INTERNAL_FORMAT], 12)) ||
+	tmp_len == 17 &&
+	my_ispunct(&my_charset_latin1, *(format_str+2)) &&
+	my_ispunct(&my_charset_latin1, *(format_str+5)) &&
+	my_ispunct(&my_charset_latin1, *(format_str+11)) &&
+	my_ispunct(&my_charset_latin1, *(format_str+14)) &&
+	(my_ispunct(&my_charset_latin1, *(format_str+8)) ||
+	 my_isspace(&my_charset_latin1, *(format_str+8))))
+      return 0;
+    break;
+    }
+  return 1;
+}