Home Page for the TeradataForum
 

 

UDF Library: 'isdate'


 

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.

Note: The earlier version of 'isdate' can be found here.


Calling:

Parameter Signature 1:

isdate(date_char,date_format);

e.g. SELECT isdate('29-03-2010','dd-mm-yyyy');


Parameter Signature 2:

isdate(date_char,date_format,[separator_char]);

e.g. SELECT isdate('29/03/2010','dd-mm-yyyy','/');


Parameter Signature 3:

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

e.g. 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:

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

Compilation:

See compile.btq


Components:





Contributed by Karl Wridgway






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