How to concatenate a date in three different columns?

Database
Enthusiast

How to concatenate a date in three different columns?

Hi,

I have a dataset where a birth date is splitted in three different columns and i need to put them into one as a date to calculate the age. How do i do this? The format of the columns are SMALLINT, and it looks like this:

Year                Month             Day

1987                 2                     14

2005                  11                  2

11 REPLIES
Teradata Employee

Re: How to concatenate a date in three different columns?

Here is one way of doing it:

SELECT (Yr (FORMAT '9(4)') (CHAR(4)))||'-'|| (Mth (FORMAT '9(2)') (CHAR(2)))||'-'|| (Dy (FORMAT '9(2)') (CHAR(2))) (DATE) AS Dt

FROM

(SELECT 1987 (INT) Yr,2 (INT) Mth,14 (INT) Dy) a

Enthusiast

Re: How to concatenate a date in three different columns?

'''' || trim(year) || '-' || trim(cast(month as integer format '99')) || '-' || trim(cast(day as integer format '99')) || ''''

select '''' || trim(1987) || '-' || trim(cast(2 as integer format '99')) || '-' || trim(cast(14 as integer format '99')) || ''''

returns 

'1987-02-14'

if you want it in a different format then you can convert the output of the above as a normal date field.

Enthusiast

Re: How to concatenate a date in three different columns?

You may try too:

you can do something like this:

select cast(cast(cast(year1 as varchar(4))|| case when character_length(cast(month1 as varchar(2)))=1 then '0'|| cast(month1 as varchar(2) else cast(month1 as varchar(2)  end|| case when character_length(cast(day1 as varchar(2)))=1 then '0'|| cast(day1 as varchar(2) else cast(day1 as varchar(2)  end as varchar(10) ) as date format 'yyyymmdd') from your_table;

Senior Apprentice

Re: How to concatenate a date in three different columns?

Instead of casting to strings and back to date you might use a numeric calculation:

SELECT 1987 AS y, 2 AS m, 14 AS d,
(y-1900)*10000 + m * 100 + d (DATE)
Enthusiast

Re: How to concatenate a date in three different columns?

oops Dieter's way is the best :)

Teradata Employee

Re: How to concatenate a date in three different columns?

I'll 2nd that. Dieter is the best. :-) 

Enthusiast

Re: How to concatenate a date in three different columns?

Thanks for helping me. However I keep getting the error: 'Ivalid date supplied for day' whatever I do.

The first step in Dieters sugestion works fine. I transform the date into a number, but when I try to make it as a date i get this error message.

This is the quiry i do:

Select Year as YMonth as Mday as d

, (y-1900)*10000 + m * 100 + d (Date)

From My_table;

Enthusiast

Re: How to concatenate a date in three different columns?

Can you plz share the ddl? It works fine :

create table mmmmmm(year1 smallint, month1 smallint, day1 smallint);

insert into mmmmmm values( 1987,2,14);

insert into mmmmmm values( .......);

select (year1-1900)*10000 +month1*100 +day1 (date) from your_tbl;

Enthusiast

Re: How to concatenate a date in three different columns?

The problem is that it works fine if I only look at part of the data, but when I want too look at more than 1047 rows i get the error message ' invalid date. Meaning row number 1048 is causing the trouble. I have not order the data som for everytime I run the query the observation number 1048 is different.