Home Page for the TeradataForum
 

 

UDF Library: 'isdate' (Old)


 

/*

   isdate.c

   Teradata User Defined Function (UDF)

   Overview
   --------
   Used to validate whether a character string is a valid date.
   It returns either a date, or NULL if the string is not a valid date.

   16-01-2010 Karl Wridgway Initial release

   Calling
   -------
   isdate(date_char,date_format,[separator_char],[century_break],[fail_mode_flag]);

   SELECT isdate('29-03-2010','dd-mm-yyyy',NULL,NULL,NULL);

   Parameters
   ----------
   date_char
       Character string containing date to be validated date_format
   date_format
       Character string containing format of date to be validated
   separator_char
       Optional. Used to specify a separator character for those date formats where one is required.
       If not supplied, default value is "-"
   century_break
       Optional. When dealing with two digit years, used to specify which years are assumed to be in
       the 20th century and which are in the 21st century. A value of 30 means years 00 to 30 are
       interpreted as 2000 to 2030, and years 31 to 99 are interpreted as 1931 to 1999.
       If not supplied, default value is 30.
   fail_mode_flag
       Optional. Used to specify behaviour of code when invalid dates are detected.
       Default behaviour is to return NULL if the date is invalid.
       If supplied with a value other than zero (TRUE), when an invalid date is detected, a
       user error code and message is supplied and the function returns a non-zero SQL error code.
       This is useful mainly for debugging the function, or working out why a date fails validation.
       If not supplied, default value is FALSE (return NULL if date is invalid)

   Supported date formats
   ----------------------
    1  ddmmyy
    2  ddmmyyyy
    3  yymmdd
    4  yyyymmdd
    5  mmddyy
    6  mmddyyyy
    7  dd-mm-yy
    8  dd-mm-yyyy
    9  yy-mm-dd
   10  yyyy-mm-dd
   11  mm-dd-yy
   12  mm-dd-yyyy
   13  ddmmmyyyy       (Jan -> Dec)
   14  dd-mmm-yyyy     (Jan -> Dec)
   15  ddmmmmyyyy      (January -> December)
   16  dd-mmmm-yyyy    (January -> December)

   Compilation
   -----------
   REPLACE FUNCTION isdate(InputDate VARCHAR(29),
                           DateFormat VARCHAR(29),
                           SeparatorChar VARCHAR(1),
                           CenturyBreak INTEGER,
                           FailModeFlag INTEGER
                          )
   RETURNS DATE
   LANGUAGE C
   NO SQL
   DETERMINISTIC
   PARAMETER STYLE SQL
   EXTERNAL
   ;

*/

#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include <string.h>
#include <stdlib.h>
#define IsNull -1
#define IsNotNull 0
#define NoSqlError "00000"

#define ERR_RC 99

void isdate     (VARCHAR_LATIN *InputDate,
              VARCHAR_LATIN *DateFormat,
              VARCHAR_LATIN *SeparatorChar,
              INTEGER *CenturyBreak,
              INTEGER *FailModeFlag,
              DATE *result,
             int *InputDate_IsNull,
             int *DateFormat_IsNull,
             int *SeparatorChar_IsNull,
             int *CenturyBreak_IsNull,
             int *FailModeFlag_IsNull,
                int *resultIsNull,
               char sqlstate[6],
               SQL_TEXT extname[129],
               SQL_TEXT specificname[129],
               SQL_TEXT errormessage[257])
{

/* Constants */

    const char valid_date_format [16][14] =
               {
                  "ddmmyy",       /*  0 */
                  "ddmmyyyy",     /*  1 */
                  "yymmdd",       /*  2 */
                  "yyyymmdd",     /*  3 */
                  "mmddyy",       /*  4 */
                  "mmddyyyy",     /*  5 */

                  "dd-mm-yy",     /*  6 */
                  "dd-mm-yyyy",   /*  7 */
                  "yy-mm-dd",     /*  8 */
                  "yyyy-mm-dd",   /*  9 */
                  "mm-dd-yy",     /* 10 */
                  "mm-dd-yyyy",   /* 11 */

                  "ddmmmyyyy",    /* 12 */
                  "dd-mmm-yyyy",  /* 13 */

                  "ddmmmmyyyy",  /* 14 */
                  "dd-mmmm-yyyy" /* 15 */
                };

    const int  length_date_format [16] =
                {
                   6, 8, 6, 8, 6, 8, 8, 10, 8, 10, 8, 10, 9, 11,
                   99,   /* variable size - will not use this value for checking length */
                   99    /* variable size - will not use this value for checking length */
                 };

/* Array holding day,month,year, sep 1 and sep 2 start positions and end positions */
    const int  date_format_pos [16][5][2] =
               {   /* day        month       year     separator 1  separator 2 */
                 { { 0 , 1 } , { 2 , 3 } , { 4 , 5 } , { 99, 0 } , { 99, 0 } } ,  /* ddmmyy */
                 { { 0 , 1 } , { 2 , 3 } , { 4 , 7 } , { 99, 0 } , { 99, 0 } } ,  /* ddmmyyyy */
                 { { 4 , 5 } , { 2 , 3 } , { 0 , 1 } , { 99, 0 } , { 99, 0 } } ,  /* yymmdd */
                 { { 6 , 7 } , { 4 , 5 } , { 0 , 3 } , { 99, 0 } , { 99, 0 } } ,  /* yyyymmdd */
                 { { 2 , 3 } , { 0 , 1 } , { 4 , 5 } , { 99, 0 } , { 99, 0 } } ,  /* mmddyy */
                 { { 2 , 3 } , { 0 , 1 } , { 4 , 7 } , { 99, 0 } , { 99, 0 } } ,  /* mmddyyyy */
                 { { 0 , 1 } , { 3 , 4 } , { 6 , 7 } , { 2 , 2 } , { 5 , 5 } } ,  /* dd-mm-yy */
                 { { 0 , 1 } , { 3 , 4 } , { 6 , 9 } , { 2 , 2 } , { 5 , 5 } } ,  /* dd-mm-yyyy */
                 { { 6 , 7 } , { 3 , 4 } , { 0 , 1 } , { 2 , 2 } , { 5 , 5 } } ,  /* yy-mm-dd */
                 { { 8 , 9 } , { 5 , 6 } , { 0 , 3 } , { 4 , 4 } , { 7 , 7 } } ,  /* yyyy-mm-dd */
                 { { 3 , 4 } , { 0 , 1 } , { 6 , 7 } , { 2 , 2 } , { 5 , 5 } } ,  /* mm-dd-yy */
                 { { 3 , 4 } , { 0 , 1 } , { 6 , 9 } , { 2 , 2 } , { 5 , 5 } } ,  /* mm-dd-yyyy */
                 { { 0 , 1 } , { 99, 0 } , { 5 , 8 } , { 99, 0 } , { 99, 0 } } ,  /* ddmmmyyyy */      /* different 1 !! */
                 { { 0 , 1 } , { 99, 0 } , { 7 , 10} , { 2 , 2 } , { 6 , 6 } } ,  /* dd-mmm-yyyy */    /* different 1 !! */
                 { { 0 , 1 } , { 99, 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } } ,  /* ddmmmmyyyy */     /* different 2 !! */
                 { { 0 , 1 } , { 99, 0 } , { 99, 0 } , { 2 , 2 } , { 99, 0 } } ,  /* dd-mmmm-yyyy */   /* different 2 !! */
               };

    const char short_month_name [12][4] =
               {
                  "jan",
                  "feb",
                  "mar",
                  "apr",
                  "may",
                  "jun",
                  "jul",
                  "aug",
                  "sep",
                  "oct",
                  "nov",
                  "dec"
                };

    const char long_month_name [12][10] =
               {
                   "january",
                   "february",
                   "march",
                   "april",
                   "may",
                   "june",
                   "july",
                   "august",
                   "september",
                   "october",
                   "november",
                   "december"
                 };


/* Variables for input parameters */

    char input_date[30];
    char date_format[30];
    char separator_char[2] = "-";      /* default */
    int  century_break = 30;           /* default */
    int  fail_mode_flag = 0;           /* 0/NULL   - failed date validation results in NULL output date
                                         Non-zero - failed date validation results in error code and message */

/* Variables for date validation */

    int  year_yyyy;
    int  month_mm;
    int  day_dd;

    char day_char[3];
    char month_char[3];
    char year_char[5];

    char month_short[4];
    char month_long[10];

/* Other variables */

    int  i,j;   /* generic looping variables */
    int  valid_date_format_index = -1;

/* ============================================================================ */

/* Initiate return values */
   *resultIsNull = IsNull;
   strcpy(sqlstate, NoSqlError);
   strcpy((char *) errormessage, " ");

/* Return null if date is null */
   if (*InputDate_IsNull == IsNull) {
      return;
   }

/* Return error if date format is null */
   if (*DateFormat_IsNull == IsNull) {
       strcpy((char *) sqlstate, "U0005");
      strcpy((char *) errormessage, "DateFormat cannot be NULL");
        return;
   }

/* Copy input parameters into local variables */

    strcpy(input_date , (char *) InputDate);
    for (i = 0; input_date[i]; i++)
       input_date[i] = tolower(input_date[i]);

    strcpy(date_format , (char *) DateFormat);
    for (i = 0; date_format[i]; i++)
       date_format[i] = tolower(date_format[i]);

   if (*SeparatorChar_IsNull != IsNull)
        strcpy(separator_char , (char *) SeparatorChar);

   if (*CenturyBreak_IsNull != IsNull)
        century_break = *CenturyBreak;

   if (*FailModeFlag_IsNull != IsNull)
        fail_mode_flag = *FailModeFlag;

/* Validate date_format */

    for ( i = 0; i < 16; i++) {
        if ( strcmp( date_format, valid_date_format[i] ) == 0 ) {
            valid_date_format_index = i;
            break;
        }
    }

    if ( valid_date_format_index == -1 ) {
       strcpy((char *) sqlstate, "U0010");
      strcpy((char *) errormessage, "DateFormat is not valid");
        return;
    }

/* Validate century_break */

    if ( century_break < 0 || century_break > 99 ) {
       strcpy((char *) sqlstate, "U0015");
      strcpy((char *) errormessage, "CenturyBreak must in the range 0 to 99");
        return;
    }

/* Validate length of input_date */

    if ( valid_date_format_index < 14 ) {    /* last 2 formats are variable length */
        if ( strlen(input_date) != length_date_format[valid_date_format_index] ) {
         strcpy((char *) sqlstate, "U0020");
         strcpy((char *) errormessage, "InputDate length is wrong. Must match DateFormat");
         return;
        }
    } else {
        if ( valid_date_format_index == 14 ) {  /* ddmmmmyyyy e.g. 19january2010 */
            if ( strlen(input_date) > 8 && strlen(input_date) < 16 ) {
            } else {
            strcpy((char *) sqlstate, "U0025");
            strcpy((char *) errormessage, "InputDate length is wrong. Must match DateFormat");
            return;
            }
        } else {
            if ( valid_date_format_index == 15 ) {  /* dd-mmmm-yyyy e.g. 19-january-2010 */
                if ( strlen(input_date) > 10 && strlen(input_date) < 18 ) {
                } else {
               strcpy((char *) sqlstate, "U0030");
               strcpy((char *) errormessage, "InputDate length is wrong. Must match DateFormat");
               return;
                }
            } else {
            strcpy((char *) sqlstate, "U0900");
            strcpy((char *) errormessage, "Internal error");
            return;
            }
        }
    }


/* ============================================================================ */

/*  MAINLINE */

/* Validate that numbers and separator chars correct and split input_date into components based on input_format  */

    /* Validate day is all digits */
    j=0;
    for ( i = date_format_pos[valid_date_format_index][0][0]; i <= date_format_pos[valid_date_format_index][0][1]; i++ ) {
        if ( ! isdigit(input_date[i]) ) {
            if ( fail_mode_flag ) {
              strcpy((char *) sqlstate, "U0035");
             strcpy((char *) errormessage, "Day is not numeric");
         }
         return;
        }
        day_char[j++] = input_date[i];
    }
    day_char[j] = '\0';
    day_dd = atoi(day_char);

    /* Validate month is all digits */
    j=0;
    for ( i = date_format_pos[valid_date_format_index][1][0]; i <= date_format_pos[valid_date_format_index][1][1]; i++ ) {
        if ( ! isdigit(input_date[i]) ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0040");
             strcpy((char *) errormessage, "Month is not numeric");
          }
          return;
        }
        month_char[j++] = input_date[i];
    }
    month_char[j] = '\0';
    month_mm = atoi(month_char);

    /* Validate year is all digits */
    j=0;
    for ( i = date_format_pos[valid_date_format_index][2][0]; i <= date_format_pos[valid_date_format_index][2][1]; i++ ) {
        if ( ! isdigit(input_date[i]) ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0045");
             strcpy((char *) errormessage, "Year is not numeric");
         }
         return;
        }
        year_char[j++] = input_date[i];
    }
    year_char[j] = '\0';
    year_yyyy = atoi(year_char);

/* Handle 2 digit year */
    if ( valid_date_format_index == 0 ||
         valid_date_format_index == 2 ||
         valid_date_format_index == 4 ||
         valid_date_format_index == 6 ||
         valid_date_format_index == 8 ||
         valid_date_format_index == 10 ) {
        if ( year_yyyy <= century_break ) {
            year_yyyy += 2000;
        } else {
            year_yyyy += 1900;
        }
    }

    /* Validate first separator is valid
       Redundant loop required to drive checking from array */
    for ( i = date_format_pos[valid_date_format_index][3][0]; i <= date_format_pos[valid_date_format_index][3][1]; i++ ) {
        if ( separator_char[0] != input_date[i] ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0050");
             strcpy((char *) errormessage, "First separator is not as expected");
         }
         return;
        }
    }

    /* Validate second separator is valid
      Redundant loop required to drive checking from array */
    for ( i = date_format_pos[valid_date_format_index][4][0]; i <= date_format_pos[valid_date_format_index][4][1]; i++ ) {
        if ( separator_char[0] != input_date[i] ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0055");
             strcpy((char *) errormessage, "Second separator is not as expected");
            }
         return;
        }
    }

/* Validate month for ddmmmyyyy */
    if ( valid_date_format_index == 12 ) {
        for ( i = 2; i < 5; i++ ) {
           month_short[i - 2] = input_date[i];
        }
        month_short[3] = '\0';

        for ( i = 0; i < 12; i++) {
            if ( strcmp( month_short, short_month_name[i] ) == 0 ) {
                month_mm = i + 1;
                break;
            }
        }
        if ( month_mm == 0 ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0060");
             strcpy((char *) errormessage, "Short format month name is not valid");
         }
         return;
        }
    }

/* Validate month for dd-mmm-yyyy */
    if ( valid_date_format_index == 13 ) {
        for ( i = 3; i < 6; i++ ) {
           month_short[i - 3] = input_date[i];
        }
        month_short[3] = '\0';

        for ( i = 0; i < 12; i++) {
            if ( strcmp( month_short, short_month_name[i] ) == 0 ) {
                month_mm = i + 1;
                break;
            }
        }
        if ( month_mm == 0 ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0065");
             strcpy((char *) errormessage, "Short format month name is not valid");
         }
         return;
        }
    }

/* Validate year is all digits for index types 14 & 15 */
    if ( valid_date_format_index == 14 || valid_date_format_index == 15 ) {
        j=0;
        for ( i = strlen(input_date) - 4; i < strlen(input_date); i++ ) {
            if ( ! isdigit(input_date[i]) ) {
                if ( fail_mode_flag ) {
                strcpy((char *) sqlstate, "U0070");
                strcpy((char *) errormessage, "Year is not numeric");
            }
            return;
            }
            year_char[j++] = input_date[i];
        }
        year_char[j] = '\0';
        year_yyyy = atoi(year_char);
    }

/* Validate separator 2 for index type 15 */
    if ( valid_date_format_index == 15 ) {
        if ( separator_char[0] != input_date[strlen(input_date) - 5] ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0075");
             strcpy((char *) errormessage, "Second separator is not as expected");
         }
         return;
        }
    }

/* Validate month for ddmmmmyyyy */
    if ( valid_date_format_index == 14 ) {
        for ( i = 2; i < strlen(input_date) - 4; i++ ) {
           month_long[i - 2] = input_date[i];
        }
        month_long[i - 2] = '\0';

        for ( i = 0; i < 12; i++) {
            if ( strcmp( month_long, long_month_name[i] ) == 0 ) {
                month_mm = i + 1;
                break;
            }
        }
        if ( month_mm == 0 ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0080");
             strcpy((char *) errormessage, "Long format month name is not valid");
         }
         return;
        }
    }

/* Validate month for dd-mmmm-yyyy */
    if ( valid_date_format_index == 15 ) {
        for ( i = 3; i < strlen(input_date) - 5; i++ ) {
           month_long[i - 3] = input_date[i];
        }
        month_long[i - 3] = '\0';

        for ( i = 0; i < 12; i++) {
            if ( strcmp( month_long, long_month_name[i] ) == 0 ) {
                month_mm = i + 1;
                break;
            }
        }
        if ( month_mm == 0 ) {
            if ( fail_mode_flag ) {
             strcpy((char *) sqlstate, "U0085");
             strcpy((char *) errormessage, "Long format month name is not valid");
         }
         return;
        }
    }

/* Validate year */

    if ( year_yyyy < 1 || year_yyyy > 9999 ) {       /* > 9999 should never happen */
        if ( fail_mode_flag ) {
          strcpy((char *) sqlstate, "U0087");
          strcpy((char *) errormessage, "Year is not valid");
      }
      return;
    }

/* Validate month */

    if ( month_mm < 1 || month_mm > 12) {
        if ( fail_mode_flag ) {
          strcpy((char *) sqlstate, "U0090");
          strcpy((char *) errormessage, "Month is not valid");
      }
      return;
    }

/* Validate day */

    switch ( month_mm ) {
       case 1: case 3: case 5: case 7: case 8: case 10: case 12:
          if (day_dd < 1 || day_dd > 31) {
                if ( fail_mode_flag ) {
                strcpy((char *) sqlstate, "U0095");
                strcpy((char *) errormessage, "Day is not valid for month");
                }
            return;
            }
           break;
       case 4: case 6: case 9: case 11:
          if (day_dd < 1 || day_dd > 30) {
                if ( fail_mode_flag ) {
                strcpy((char *) sqlstate, "U0100");
                strcpy((char *) errormessage, "Day is not valid for month");
            }
            return;
            }
           break;
       case 2:
          if ( year_yyyy % 400 == 0 || ( year_yyyy % 100 != 0 && year_yyyy % 4 == 0 )) { /* leap year */
               if (day_dd < 1 || day_dd > 29) {
                    if ( fail_mode_flag ) {
                   strcpy((char *) sqlstate, "U0105");
                   strcpy((char *) errormessage, "Day is not valid for month");
               }
               return;
                }
         } else {
               if (day_dd < 1 || day_dd > 28) {
                    if ( fail_mode_flag ) {
                   strcpy((char *) sqlstate, "U0110");
                   strcpy((char *) errormessage, "Day is not valid for month");
               }
               return;
                }
         }
           break;
      default:
         strcpy((char *) sqlstate, "U0905");
         strcpy((char *) errormessage, "Internal error");
         return;
         break;
   }

/* Format output_date in internal Teradata format
   ((YEAR - 1900) * 10000 ) + (MONTH * 100) + DAY     */

   *result = (( year_yyyy - 1900 ) * 10000 ) + ( month_mm * 100) + day_dd;
   *resultIsNull = IsNotNull;

}








 
  Top Home Privacy Feedback  
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky
Copyright 2016 - All Rights Reserved
Last Modified: 28 Jun 2020