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 > > > >
In article <81e462$ak9$1@news.xmission.com>, SAMPATH <sampath@gulfins.com.kw> wrote: How about: SELECT YEAR(field1) || field2[3,4]... if field2 is type char. Art S. Kagel > 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.
In article <81e462$ak9$1@news.xmission.com>, SAMPATH <sampath@gulfins.com.kw> wrote: How about: SELECT YEAR(field1) || field2[3,4]... if field2 is type char. Art S. Kagel > 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.
SELECT YEAR(datecol) * 100 + MONTH(datecol) AS YM, ... It's a number, not a string, and it sorts correctly. SAMPATH wrote: > I had tried this already ,but the problem is that I am getting > > 19995 for 20/5/1999 > [...snip...] > > > -----Original Message----- > > From: Rekaish Bhardwaj [SMTP:RekaishB@compans.com] > > Sent: 23 November, 1999 01:56 ã > > > > Assuming table called xyz with a date column abc try > > > > SELECT YEAR(abc)||MONTH(abc) > > FROM xyz > > > > -----Original Message----- > > From: SAMPATH [mailto:sampath@gulfins.com.kw] > > Sent: 23 November 1999 10:02 > > > > 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. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>