Home Page for the TeradataForum
 

 

UDF Library: 'isdate'

Component: 'isdate3.c'


 
<< isdate2.c  

/*

   isdate3.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
   23-05-2011  Karl Wridgway  Include d-m-yyyy, d-m-yy, yyyy-m-d and yy-m-d formats

   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
       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)
   17  d-m-yy
   18  d-m-yyyy
   19  yy-m-d
   20  yyyy-m-d

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

*/

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

#define ERR_RC 99

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

/* Constants */

    const char valid_date_format [20][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 */
                                              "d-m-yy",       /* 16 */
                                              "d-m-yyyy",     /* 17 */
                                              "yy-m-d",       /* 18 */
                                              "yyyy-m-d"      /* 19 */
                                            };

    const int  length_date_format [20] =    {
                                              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 */
                                              99,   /* variable size - will not use this value for checking length */
                                              99,   /* variable size - will not use this value for checking length */
                                              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 [20][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 !! */
                                              { { 99 , 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } } , /* d-m-yy */         /* dummy entry !! */
                                              { { 99 , 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } } , /* d-m-yyyy */       /* dummy entry !! */
                                              { { 99 , 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } } , /* yy-m-d */         /* dummy entry !! */
                                              { { 99 , 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } , { 99, 0 } }   /* yyyy-m-d */       /* dummy entry !! */
                                            };


/* Array holding component date field types for date format index values 16 -> 19
   Values for other date formats stored for simplicity
*/
    const char  date_format_field_types [20][3] = {   /* field 1    field 2    field 3 */
                                              { 'd' , 'm' , 'y' } ,  /* ddmmyy */               /* dummy entry !! */
                                              { 'd' , 'm' , 'y' } ,  /* ddmmyyyy */             /* dummy entry !! */
                                              { 'y' , 'm' , 'd' } ,  /* yymmdd */               /* dummy entry !! */
                                              { 'y' , 'm' , 'd' } ,  /* yyyymmdd */             /* dummy entry !! */
                                              { 'm' , 'd' , 'y' } ,  /* mmddyy */               /* dummy entry !! */
                                              { 'm' , 'd' , 'y' } ,  /* mmddyyyy */             /* dummy entry !! */
                                              { 'd' , 'm' , 'y' } ,  /* dd-mm-yy */             /* dummy entry !! */
                                              { 'd' , 'm' , 'y' } ,  /* dd-mm-yyyy */           /* dummy entry !! */
                                              { 'y' , 'm' , 'd' } ,  /* yy-mm-dd */             /* dummy entry !! */
                                              { 'y' , 'm' , 'd' } ,  /* yyyy-mm-dd */           /* dummy entry !! */
                                              { 'm' , 'd' , 'y' } ,  /* mm-dd-yy */             /* dummy entry !! */
                                              { 'm' , 'd' , 'y' } ,  /* mm-dd-yyyy */           /* dummy entry !! */
                                              { 'd' , 'm' , 'y' } ,  /* ddmmmyyyy */            /* dummy entry !! */
                                              { 'd' , 'm' , 'y' } ,  /* dd-mmm-yyyy */          /* dummy entry !! */
                                              { 'd' , 'm' , 'y' } ,  /* ddmmmmyyyy */           /* dummy entry !! */
                                              { 'd' , 'm' , 'y' } ,  /* dd-mmmm-yyyy */         /* dummy entry !! */
                                              { 'd' , 'm' , 'y' } ,  /* d-m-yy */
                                              { 'd' , 'm' , 'y' } ,  /* d-m-yyyy */
                                              { 'y' , 'm' , 'd' } ,  /* yy-m-d */
                                              { 'y' , 'm' , 'd' }    /* yyyy-m-d */
                                            };

    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 date_part_char[30];

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

    int date_sep_pos[2];

/* Other variables */

    int  i,j,k;                         /* 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 < 20; 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 6 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 {
            if ( valid_date_format_index == 16 ) {  /* d-m-yy e.g. 1-12-11 */
                if ( strlen(input_date) > 5 && strlen(input_date) < 9 ) {
                } else {
                    strcpy((char *) sqlstate, "U0030");
                    strcpy((char *) errormessage, "InputDate length is wrong. Must match DateFormat");
                    return;
                }
        } else {
            if ( valid_date_format_index == 17 ) {  /* d-m-yyyy e.g. 20-12-2011 */
                if ( strlen(input_date) > 7 && strlen(input_date) < 11 ) {
                } else {
                    strcpy((char *) sqlstate, "U0030");
                    strcpy((char *) errormessage, "InputDate length is wrong. Must match DateFormat");
                    return;
                }
        } else {
            if ( valid_date_format_index == 18 ) {  /* yy-m-d e.g. 11-1-11 */
                if ( strlen(input_date) > 5 && strlen(input_date) < 9 ) {
                } else {
                    strcpy((char *) sqlstate, "U0030");
                    strcpy((char *) errormessage, "InputDate length is wrong. Must match DateFormat");
                    return;
                }
        } else {
            if ( valid_date_format_index == 19 ) {  /* yyyy-m-d e.g. 2011-1-9 */
                if ( strlen(input_date) > 7 && strlen(input_date) < 11 ) {
                } 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
   This is effectively only done for fixed length dates - based on values in driving array
*/

    /* 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;
        }
    }

    /* Now process varying length date formats - index 16-19 */
    if ( valid_date_format_index == 16 ||
         valid_date_format_index == 17 ||
         valid_date_format_index == 18 ||
         valid_date_format_index == 19 ) {

        j = -1; /* index of last separator found in string */

        /* Loop through date and find separators  */
        for ( i = 0; i < strlen(input_date); i++ ) {
            if ( separator_char[0] == input_date[i] ) {
                j++;
                /* If more than 2 separators found, abort */
                if ( j > 1 ) {
                    if ( fail_mode_flag ) {
                        strcpy((char *) sqlstate, "U0051");
                        strcpy((char *) errormessage, "More than two separator found");
                    }
                    return;
                } else {
                    date_sep_pos[j] = i;
                }
            }
        }
        /* Error if not exactly 2 separators found */
        if ( j != 1 ) {
            if ( fail_mode_flag ) {
                strcpy((char *) sqlstate, "U0052");
                strcpy((char *) errormessage, "Not enough separators found");
            }
            return;
        }

        k = 0;   /* counter of the number of separators found so far - used to access date field component array */
        j = 0;   /* index of string array holding char version of current date component */
        /* Now check that each of the 3 date components are all numeric and move to appropriate date component variable for further checking */
        for ( i = 0; i <= strlen(input_date); i++ ) {
            if ( i == strlen(input_date) || i == date_sep_pos[0] || i == date_sep_pos[1] ) {
                date_part_char[j] = '\0';
                switch ( date_format_field_types [valid_date_format_index][k] ) {
                    case 'd':   /* day */
                        day_dd = atoi(date_part_char);
                        break;
                    case 'm':   /* month */
                        month_mm = atoi(date_part_char);
                        break;
                    case 'y':   /* year */
                        year_yyyy = atoi(date_part_char);
                        break;
                    default:
                        strcpy((char *) sqlstate, "U0906");
                        strcpy((char *) errormessage, "Internal error");
                        return;
                        break;
                }
                k++;
                j = 0;
            } else {
                if ( ! isdigit(input_date[i]) ) {
                    if ( fail_mode_flag ) {
                        strcpy((char *) sqlstate, "U0046");
                        strcpy((char *) errormessage, "Date portion is not numeric");
                    }
                    return;
                }
                date_part_char[j] = input_date[i];
                j++;
            }
        }
    }

    /* Handle 2 digit year */
    if ( valid_date_format_index == 16 ||
         valid_date_format_index == 18  ) {
        if ( year_yyyy <= century_break ) {
            year_yyyy += 2000;
        } else {
            year_yyyy += 1900;
        }
    }

    /* 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;

}


<< isdate2.c  





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