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