Need to cast an invalid date to a valid

Analytics
Enthusiast

Need to cast an invalid date to a valid

Hi All,
I have this problem, we are loading come operater entered data in to a teradata table.
The column contains different data and based on the condition its loaded.
On a few condition the column would contain date which is cast into date format dd/mm/yyyy and loaded.
But once in a while we get date for example 1/02/2009 or 01/2/2009.
Which gives an invalid date error and we have to manually edit it, is there andy casting function that can convert these dates to teh vallid 01/02/2009 format
3 REPLIES
rgs
Enthusiast

Re: Need to cast an invalid date to a valid

What you want to do is to detect the bad date string and convert it to a good one. If the problem is truly just the missing leading zero on the DD or MM field then it is pretty easy. Of course it does not fix just plain bad dates that go outside the valid date ranges or just incorrect entries. The case expression looks for the following:

MM/DD/YYYY : this is normal and it just converts the string to a date

M/D/YYYY : missing leading zero it adds the leading zero to month and day and converts to date

MM/D/YYYY : missing leading zero on day. It adds the leading zero and converts to a date.

M/DD/YYYY : missing leading zero on month. It adds the leading zero and converts to date.

If there is any other combination then it converts it to 01/01/0001. This is arbitrary on my part, you need to figure out what to do in that case.

Here is a sample query:

SELECT datestr,
CASE WHEN
(POSITION('/' IN datestr) = 3) AND
(POSITION('/' IN SUBSTRING(datestr FROM POSITION('/' IN datestr)+1 FOR 3)) = 3)
THEN
CAST (datestr AS DATE FORMAT 'DD/MM/YYYY')
WHEN
(POSITION('/' IN datestr) = 2) AND
(POSITION('/' IN SUBSTRING(datestr FROM POSITION('/' IN datestr)+1 FOR 3)) = 2)
THEN
CAST (('0' || SUBSTRING(datestr FROM 1 FOR 2) || '0' || SUBSTRING(datestr FROM 3 FOR 6)) AS DATE FORMAT 'DD/MM/YYYY')
WHEN
(POSITION('/' IN datestr) = 3) AND
(POSITION('/' IN SUBSTRING(datestr FROM POSITION('/' IN datestr)+1 FOR 3)) = 2)
THEN
CAST ((SUBSTRING(datestr FROM 1 FOR 3) || '0' || SUBSTRING(datestr FROM 4 FOR 6)) AS DATE FORMAT 'DD/MM/YYYY')
WHEN
(POSITION('/' IN datestr) = 2) AND
(POSITION('/' IN SUBSTRING(datestr FROM POSITION('/' IN datestr)+1 FOR 3)) = 3)
THEN
CAST (('0' || SUBSTRING(datestr FROM 1 FOR 2) || SUBSTRING(datestr FROM 3 FOR 7)) AS DATE FORMAT 'DD/MM/YYYY')
ELSE
CAST ('01/01/0001' AS DATE FORMAT 'DD/MM/YYYY')
END
FROM tst_date_tbl;

*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

datestr CASE expression
---------- ------------------
30/11/2009 09/11/30
4/7/2009 09/07/04
11/8/2009 09/08/11
3/10/2009 09/10/03
04//2009 01/01/01

Sorry this web site takes out multiple spaces and other stuff -- nothing that I can do about that.

Re: Need to cast an invalid date to a valid

Hi, 

I am working with Business Objects Universe and using teradata database. 

I m getting the invalid date error while running the report. 

There are 2 separate fields date and time. Date is in Date format and time is in char format. I just created 3 different objects which changes the date into char field, time also into char field and concatenated both of them and this field is also character field. i.e.  Output is in the format "2009-07-01 07:54" on the report.

Now, I need to do this for IN and OUT times and calculate the difference between two time stamps. When I was trying to change the concatenated field to a date field in the universe, it parses fine. When I use the same object in the report, I m getting invalid date error. 

Please help me!! 

Thanks in advance!! 

Teradata Employee

Re: Need to cast an invalid date to a valid

Try by adding ":00" at the end of your concatenated field for seconds .... e.g. "2009-07-01 07:54:00" and it should be fine.

HTH!