All Forums Database
Wildc 6 posts Joined 10/14
02 Oct 2014
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

frnewbrough 41 posts Joined 03/08
02 Oct 2014

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

Tuen 44 posts Joined 07/05
02 Oct 2014

'''' || 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.

Raja_KT 1246 posts Joined 07/09
02 Oct 2014

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;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
02 Oct 2014

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)

 

Dieter

Raja_KT 1246 posts Joined 07/09
02 Oct 2014

oops Dieter's way is the best :)

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

frnewbrough 41 posts Joined 03/08
02 Oct 2014

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

Wildc 6 posts Joined 10/14
02 Oct 2014

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)
 
From My_table;

Raja_KT 1246 posts Joined 07/09
03 Oct 2014

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;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Wildc 6 posts Joined 10/14
03 Oct 2014

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.

Raja_KT 1246 posts Joined 07/09
03 Oct 2014

Then you if you can share more details, like your ddl, what you have and what is the expected output, then it will be easier to interpret.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
03 Oct 2014

That's simple, there's an invalid date.
This function will return a NULL for invalid dates:

REPLACE FUNCTION fnc_try_ymd_to_date(y INTEGER, m INTEGER, d INTEGER)
RETURNS DATE
SPECIFIC fnc_ymd_to_date_check
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
   CASE
      WHEN y BETWEEN 1 AND 9999
       AND m BETWEEN 1 AND 12
       AND d BETWEEN 1 AND CASE
                              WHEN m IN (1,3,5,7,8,10,12) THEN 31
                              WHEN m IN (4,6,9,11) THEN 30
                              ELSE 28 + CASE
                                           WHEN (y MOD 4 = 0 AND y MOD 100 <> 0) 
                                             OR  y MOD 400 = 0 
                                           THEN 1
                                           ELSE 0
                                        END
                           END
      THEN ((y - 1900) * 10000 + (m * 100) + d (DATE))
   END
;

 

Dieter

You must sign in to leave a comment.