database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Devel 21117 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 21117
MySQL UDF -- valid_date
작성자
정재익(advance)
작성일
2004-02-24 09:50
조회수
8,813

유효한 날짜인지를 check 하는 함수

 

====================================

Summary: Checks date, datetime, numeric, or numeric string entry for a proper day and time based on the year and month. Also supports an optional parm of: MAX_DAYS, USE_LAST_DAY, USE_LAST_TIME, or FORMAT_RETURN.

====================================

 

// udf_chkdate.cc
// valid_date - written by Jim Faucette 1999 (jimf@awod.com)
//
// Entered into the Public Domain 6/1/99 (oops 1999-06-01 :)
// 
// Usage:
//   valid_date(time [, mode])
//
// Requires one argument time:
//   It may be a date, date time, int, or string of ints.
//   Minimum input is YYMMDD
//   Ints with 6 or 7 digits are treated as 2 digit years. All other
//   lengths are treated as 4 digit years.
//  
// Optional argument mode:
//   "MAX_DAYS" returns a month's max days for a given year and month
//   "USE_LAST_DAY" returns the given YYYY/MM/max_days
//   "USE_LAST_TIME" returns the given YYYY/MM/max_days 23:59:59
//   "FORMAT_RETURN" returns numeric arguments as MySQL formatted strings
//   If mode is not supported (or NULL), then NULL is returned.
//
// Return value (with no mode):
//   If all the elements of the date and time are in the proper range for
//   the given year and month then the input is returned. Otherwise NULL
//   is returned.
//
// Compiling:
//   This file was created in ../mysql-3.22.20a/sql/. Under FreeBSD 3.1
//   the following worked perfectly:
//
//   gcc -DMYSQL_SERVER  -DDEFAULT_MYSQL_HOME="\"/usr/local\"" \
//   -DDATADIR="\"/usr/local/var\"" \
//   -DSHAREDIR="\"/usr/local/share/mysql\"" -DHAVE_CONFIG_H \
//   -I./../include  -I./../regex  -I. -I../include -I.. -I. \
//   -fno-implicit-templates -pthread -shared \
//   -DDBUG_OFF -o udf_chkdate.so udf_chkdate.cc
//
//   udf_chkdate.so was then copied to /usr/local/lib/
//
// Reminder:
//   Before you can use any UDF MySQL must be told about it: 
//     CREATE FUNCTION valid_date RETURNS STRING SONAME "udf_chkdate.so";
//
//   If you decide to discontinue use:
//     DROP FUNCTION valid_date;
//
// Acknowledgements: The staff at MySQL.

#ifdef STANDARD
#include <stdio.h>
#include <string.h>
#else
#include <global.h>
#include <my_sys.h>
#endif
#include <mysql.h>
#include <mysql_priv.h>
#include <m_ctype.h>

extern "C" {
   // functions for chkdate
my_bool valid_date_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
char *valid_date(UDF_INIT *initid, UDF_ARGS *args, char *result,
      unsigned long *length, char *is_null, char *error);
}

 
int days_in_mon[]= {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};

#define MAXLEN 21

my_bool valid_date_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
  if(args->arg_count < 1) {
    strcpy(message,"valid_date: one required arg and two optionals");
    return 1;
  }
  initid->max_length = MAXLEN;             // 0000-00-00 00:00:00
  initid->maybe_null = 0;
  args->arg_type[0] = STRING_RESULT;
  if(args->arg_count > 1)
    args->arg_type[1] = STRING_RESULT;

  return 0;
}


char *valid_date(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length, char *is_null, char *error) {
  struct tm a_time;
  int leap = 0, max_days, x, y;
  char tmp[30], *save_args = NULL;

  if(args->args[0] == NULL || (args->arg_count > 1 && args->args[1] == NULL)) {
    *is_null = 1;
    return(result);
  }

  memset(&a_time, 0, sizeof(struct tm));      
  if(strchr(args->args[0], '-') == NULL) {   // its all numbers
    memset(tmp, 0, sizeof(tmp));
    save_args = args->args[0];
    if(args->lengths[0] == 6 || args->lengths[0] == 7) {  // 2 digit year
      sprintf(tmp, "%c%c-%c%c-%c%c", args->args[0][0],
              args->args[0][1], args->args[0][2], args->args[0][3],
              args->args[0][4], args->args[0][5]);
    }
    else {                                             // 4 digit year
      for(x = y = 0; x < args->lengths[0] && x < 14; x++) {
        tmp[y++] = args->args[0][x];
        if(y == 4 || y == 7)
           tmp[y++] = '-';
        else if(y == 10)
           tmp[y++] = ' ';
        else if(y == 13 || y == 16)
           tmp[y++] = ':'; 
      }       
    }
    args->args[0] = tmp;
    args->lengths[0] = strlen(args->args[0]);
  }

                                             // check 4 digit then 2
  if(strptime(args->args[0], " %Y-%m-%d %T", &a_time) != NULL ||
     strptime(args->args[0], " %y-%m-%d %T", &a_time) != NULL ||
     (a_time.tm_year && a_time.tm_mon)) {
    if(!a_time.tm_year) {
      *is_null = 1;
      return(result);
    }

      if((a_time.tm_year & 3) == 0 &&
         (a_time.tm_year%100 || (a_time.tm_year%400 == 0 && a_time.tm_year)))
        leap = 1;
 
      if(a_time.tm_mon+1 == 2 && leap)
        max_days = 29;
      else
        max_days = days_in_mon[a_time.tm_mon];

      if(args->arg_count > 1) {
         x = 0;
         if(!strcmp(args->args[1], "MAX_DAYS"))
           x = sprintf(result, "%d", max_days);
         else if(!strcmp(args->args[1], "USE_LAST_DAY"))
           x = sprintf(result, "%04d-%02d-%02d", a_time.tm_year+1900,
                    a_time.tm_mon+1, max_days);
         else if(!strcmp(args->args[1], "USE_LAST_TIME"))
           x = sprintf(result, "%04d-%02d-%02d 23:59:59",
                   a_time.tm_year+1900, a_time.tm_mon+1, max_days);
         else if(strcmp(args->args[1], "FORMAT_RETURN"))
           *is_null = 1;
         if(x) {
           *length = strlen(result); 
           return(result);
         }
      }

      if(a_time.tm_mday > max_days || a_time.tm_mday < 1 ||
         a_time.tm_hour > 23 || a_time.tm_min > 59 || a_time.tm_sec > 59)
        sprintf(error,"invalid_date:");
      else
      {
        if(save_args == NULL || (save_args != NULL &&
           args->arg_count > 1 && !strcmp(args->args[1],"FORMAT_RETURN")))
          memcpy(result, args->args[0], strlen(args->args[0])+1);
        else
          memcpy(result, save_args, strlen(save_args)+1);
        *length = strlen(result); 
        return(result);
      }
  }
  else
    strcpy(error, "invalid_date input");

  *is_null = 1;   
  *length = 0; 
  return(&(result[0] = 0));
}

[Top]
No.
제목
작성자
작성일
조회
21128MySQL UDF -- unprefix
정재익
2004-02-24
10194
21125MySQL UDF -- ip address manipulation
정재익
2004-02-24
8955
21119MySQL UDF -- corba_string
정재익
2004-02-24
8396
21117MySQL UDF -- valid_date
정재익
2004-02-24
8813
21114MySQL UDF - il2_to_cp1250
정재익
2004-02-24
6716
21113MySQL UDF - inet_ntoa() 의 구현
정재익
2004-02-24
7135
21063MySQL UDF - zlib string compression
정재익
2004-02-18
7075
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다