Many databases such as SQL Server supports isdate function. Amazon Redshift supports many date functions. We have already discussed Redshift date functions in my other post, “Commonly used Redshift Date Functions“. You may have noticed, there is no function to validate date and timestamp values in Amazon Redshift. In this article, we will check how to validate date in Redshift using isdate Python user defined function with an example.
Redshift Validate Date using isdate User Defined Function
The best part about Redshift is that you can write user defined function using SQL or Python. Redshift support wide range of modules.
You can use the Python datetime module with exception handling to validate if input string is date. We used Python because you can handle the exceptions that may occur if date is not a valid.
Redshift ISDATE User Defined Function
Firstly, you need to create an isdate Redshift user defined function that will validate date function.
Following is the isdate function in Redshift using Python.
CREATE OR REPLACE FUNCTION isdate (x1 varchar)
RETURNS float
IMMUTABLE as $$
import datetime
try:
datetime.datetime.strptime(x1, '%Y-%m-%d')
return True
except ValueError:
return False
$$ LANGUAGE plpythonu;
The idea is to extract date part from the input string. It will return True if date is valid, otherwise False.
Following is the example of using isdate function.
dev=# select isdate('2020-01-1');
isdate
--------
1
(1 row)
dev=# select isdate('2020-011');
isdate
--------
0
(1 row)
You can also add Python user defined function inside Redshift UDFs.
For example,
CREATE OR REPLACE FUNCTION isdate (x1 varchar)
RETURNS float
IMMUTABLE as $$
def isvalid(date_text):
import datetime
try:
datetime.datetime.strptime(date_text, '%Y-%m-%d')
return True
except ValueError:
return False
return isvalid(x1)
$$ LANGUAGE plpythonu;
You can read more about Redshift user defined function in my other article, Redshift User Defined Functions Examples
Validate Date Time Values in Redshift
You can also enhance the user defined function to validate date time values. You can use an enhanced udf to validate input string containing date time values.
For example, consider following udf to validate date time values.
CREATE OR REPLACE FUNCTION isdate (x1 varchar)
RETURNS float
IMMUTABLE as $$
import datetime
try:
datetime.datetime.strptime(x1, '%Y-%m-%d %H:%M:%S')
return True
except ValueError:
return False
$$ LANGUAGE plpythonu;
Following is the example which validates date time values.
dev=# select isdate('2020-01-01 10:10:10');
isdate
--------
1
(1 row)
Related Articles,
- How to use Amazon Redshift Replace Function?
- Commonly used Redshift Date Functions and Examples
- Redshift Integer Value Check – Methods and Examples
- Redshift Interval Data Types and Conversion Examples
- Teradata Number Date Alternative in Redshift
Hope this helps 🙂