Amazon Redshift Validate Date - isdate Function

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)