RE:

Hai, You are right . I want the output to be ordered by year & months across various years like 199504 199507 199812 199901 199911. Otherwise it will look like, 19954 19957 19991 199812 199911. Thanks a lot. Sam > -----Original Message----- > From: Rekaish Bhardwaj [SMTP:RekaishB@compans.com] > Sent: 23 November, 1999 04:18 ' > To: 'SAMPATH' > Subject: RE: > > I'm not sure if its possible without resorting to a stored procedure > or 4gl. > However, since you mentioned 'order by', > is your original problem to do with months not appearing in the > correct > order in a output > because you get something like > 1 > 10 > 11 > 12 > 2 > 3 > 4 > 5 > ????? > > -----Original Message----- > From: SAMPATH [mailto:sampath@gulfins.com.kw] > Sent: 23 November 1999 12:51 > To: informix-list@iiug.org > Cc: Rekaish Bhardwaj > Subject: RE: > > > I had tried this already ,but the problem is that I am getting > > 19995 for 20/5/1999 > 19996 for 10/6/1999 > 19942 for 10/2/1999 > and > 199910 for 10/10/1999 > > so my output 'order by' was failing. > > I want 199905 > 199906 > 199910 ie. month in 2 digits. > > Thanks > > Sam > > > > -----Original Message----- > > From: Rekaish Bhardwaj [SMTP:RekaishB@compans.com] > > Sent: 23 November, 1999 01:56 ' > > To: 'SAMPATH' > > Subject: RE: > > > > Assuming table called xyz with a date column abc try > > > > SELECT YEAR(abc)||MONTH(abc) > > FROM xyz > > > > Then assuming the environment variable DBDATE is set to dmy4/ you > > should get > > 4 digits for the year and the > > month will be concatenated to the year. You will not need the other > > column > > with the year and month > > > > Regards > > Rekaish > > > > -----Original Message----- > > From: SAMPATH [mailto:sampath@gulfins.com.kw] > > Sent: 23 November 1999 10:02 > > To: informix-list@iiug.org > > Subject: > > > > > > Our database has got a number of tables with dates and year&month ( > 4 > > Chars ) as two different columns, > > > > ie. > > Field1 Field2 > > > > 20/12/1998 9812 > > 10/11/1999 9911 > > 10/01/1993 9301 > > > > How can I select year and month as 6 characters using a simple query > > on > > this table. > > I would like to get an output like > > > > Field1 Field2 > > > > 20/12/1998 199812 > > 10/11/1999 199911 > > 10/01/1993 199301. > > > > This is urgent. Any suggestions will be greatly appreciated. > > > > > > Thanks > > > > Sam > > > > > > > >

Taking the clues from Rekaish here is what I got: create temp table t1 (f1 date, f2 char(4)); insert into t1 values ("12/20/1998","9812"); insert into t1 values ("11/10/1999","9911"); insert into t1 values ("01/10/1993","9301");select year(f1)||f2[3,4] from t1; (expression) 199812 199911 199301 HTH Ram S. In article <81e9jm$cd0$1@news.xmission.com>, SAMPATH <sampath@gulfins.com.kw> wrote: > > Hai, > You are right . I want the output to be ordered by year & > months across various years like > > 199504 > 199507 > 199812 > 199901 > 199911. Otherwise it will look like, > > 19954 > 19957 > 19991 > 199812 > 199911. > > Thanks a lot. > > Sam > > > -----Original Message----- > > From: Rekaish Bhardwaj [SMTP:RekaishB@compans.com] > > Sent: 23 November, 1999 04:18 ' > > To: 'SAMPATH' > > Subject: RE: > > > > I'm not sure if its possible without resorting to a stored procedure > > or 4gl. > > However, since you mentioned 'order by', > > is your original problem to do with months not appearing in the > > correct > > order in a output > > because you get something like > > 1 > > 10 > > 11 > > 12 > > 2 > > 3 > > 4 > > 5 > > ????? > > > > -----Original Message----- > > From: SAMPATH [mailto:sampath@gulfins.com.kw] > > Sent: 23 November 1999 12:51 > > To: informix-list@iiug.org > > Cc: Rekaish Bhardwaj > > Subject: RE: > > > > > > I had tried this already ,but the problem is that I am getting > > > > 19995 for 20/5/1999 > > 19996 for 10/6/1999 > > 19942 for 10/2/1999 > > and > > 199910 for 10/10/1999 > > > > so my output 'order by' was failing. > > > > I want 199905 > > 199906 > > 199910 ie. month in 2 digits. > > > > Thanks > > > > Sam > > > > > > > -----Original Message----- > > > From: Rekaish Bhardwaj [SMTP:RekaishB@compans.com] > > > Sent: 23 November, 1999 01:56 ' > > > To: 'SAMPATH' > > > Subject: RE: > > > > > > Assuming table called xyz with a date column abc try > > > > > > SELECT YEAR(abc)||MONTH(abc) > > > FROM xyz > > > > > > Then assuming the environment variable DBDATE is set to dmy4/ you > > > should get > > > 4 digits for the year and the > > > month will be concatenated to the year. You will not need the other > > > column > > > with the year and month > > > > > > Regards > > > Rekaish > > > > > > -----Original Message----- > > > From: SAMPATH [mailto:sampath@gulfins.com.kw] > > > Sent: 23 November 1999 10:02 > > > To: informix-list@iiug.org > > > Subject: > > > > > > > > > Our database has got a number of tables with dates and year&month ( > > 4 > > > Chars ) as two different columns, > > > > > > ie. > > > Field1 Field2 > > > > > > 20/12/1998 9812 > > > 10/11/1999 9911 > > > 10/01/1993 9301 > > > > > > How can I select year and month as 6 characters using a simple query > > > on > > > this table. > > > I would like to get an output like > > > > > > Field1 Field2 > > > > > > 20/12/1998 199812 > > > 10/11/1999 199911 > > > 10/01/1993 199301. > > > > > > This is urgent. Any suggestions will be greatly appreciated. > > > > > > > > > Thanks > > > > > > Sam > > > > > > > > > > > > > Sent via Deja.com http://www.deja.com/ Before you buy.