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
'''' || 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')) || ''''
if you want it in a different format then you can convert the output of the above as a normal date field.
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;
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)
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 Y, Month as M, day as d
, (y-1900)*10000 + m * 100 + d (Date)
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;
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.