RE:

--Boundary_(ID_mMZayScUXbgDxduBHBpOAg) Content-type: text/plain; charset=us-ascii Content-disposition: inline Content-transfer-encoding: 7BIT Sampath/Art If you have 7.3, you have some new functions you can use, like this: select YEAR(field1) || SUBSTR('0'||MONTH(field), LENGTH(''||MONTH(field)), 2) from table; or simpler still select YEAR(field1) || LPAD(MONTH(field1), 2, "0") from table; PS - the first is an old trick I used during my dBase III programming days, when they did not have lpad and rpad functions. Then I remembered seeing LPAD in the manuals, hence the second SQL. HTH Sujit akagel@my-deja.com on 11/23/99 07:12:14 AM Please respond to akagel@my-deja.com To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: RE: --Boundary_(ID_mMZayScUXbgDxduBHBpOAg) Content-type: text/plain; charset=iso-8859-1 Content-disposition: inline Content-transfer-encoding: quoted-printable 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 =E3 > > 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 quer= y > > 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. = --Boundary_(ID_mMZayScUXbgDxduBHBpOAg)--