Re: SQL convert number into the date

Probably because the wbzdatum column is an integer not a string. Cast it to a string first: SELECT ar1.artikel, ar2.wbzdatum, MDY(SUBSTR(ar2.wbzdatum::char(6),3,2), SUBSTR(ar2.wbzdatum::char(6),1,2), 2000+SUBSTR(ar2.wbzdatum::char(6),5,2) ) as Test FROM fyar1sta ar1, fyar2sta ar2 WHERE ar1.artikel = ar2.artikel; However, IB that the following will be faster, test both: SELECT ar1.artikel, ar2.wbzdatum, (TO_CHAR( ar2.wbzdatum, '&&&&&&' ))::date as Test FROM fyar1sta ar1, fyar2sta ar2 WHERE ar1.artikel = ar2.artikel; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Nov 28, 2012 at 3:54 AM, Ntoni Nsimba <ntoni.nsimba@esw-gmbh.de>wrote: > ** > I added the statement. > my SQl statement is the following > SELECT > ar1.artikel, > ar2.wbzdatum, > MDY(SUBSTR(ar2.wbzdatum,length(ar2.wbzdatum)-3,2), > SUBSTR(ar2.wbzdatum,1,length(ar2.wbzdatum)-4), > 2000+SUBSTR(ar2.wbzdatum,length(ar2.wbzdatum)-1,2) ) as Test > FROM fyar1sta ar1, fyar2sta ar2 > WHERE ar1.artikel = ar2.artikel; > > if run my SQL file with DBACCESS and get the following error > > *674: Procedure (SUBSTR) not found* > > 111:ISAM Error no record found > error in Linie 4 > near character position 4 > > SUBSTR is a standard function . why is not found. > > thank you > > -----Ursprüngliche Nachricht----- > *Von:* Fernando Nunes [mailto:domusonline@gmail.com] > *Gesendet:* Dienstag, 27. November 2012 15:25 > *An:* Art Kagel > *Cc:* Ntoni Nsimba; IIUG Informix List > *Betreff:* Re: SQL convert number into the date > > Sorry for previous... > > I think something like this should work: > > MDY( > substr(column, length(column) -3,2), > substr(column,1,length(column) -4), > 2000 + substr(column,length(column)-1,2) > ) > should work... Not pretty... but give or take some error it could work > because apparently the months and year seem to have always 2 digits) > > > > On Tue, Nov 27, 2012 at 11:23 AM, Art Kagel <art.kagel@gmail.com> wrote: > >> There is no direct conversion or cast from a string containing MMDDYY to >> a date or datetime type because you do not have a leading zero for dates >> with a month number less than 10. This is going to make the process >> harder. It's going to be a multi-step process: >> >> >> 1. Add a new DATE or DATETIME YEAR TO DAY type column >> 2. Write a stored function to pick out the parts of the date (Year, >> month, & day) and use them to assemble a proper date string as "YY/MM/DD", >> "MM/DD/YY", or "DD/MM/YY" depending on how you have the DBDATE environment >> variable set and cast that to a date/datetime, or use the MDY() function to >> make a date/datetime out of the parts and return the date/datetime as >> appropriate. >> 3. Use the function to populate the new column from the old one. >> 4. Drop the old column. >> 5. If required, rename the new column to the old column's name. >> >> Hmm alternatively, you could: >> >> 1. expand the current column length to accommodate the date field >> separator characters, >> 2. have the stored procedure return the properly formatted date >> string, >> 3. use the function to update the current column with a formatted >> date, >> 4. then alter the column type to DATE or DATETIME YEAR TO DAY instead. >> >> Art >> >> Art S. Kagel >> Advanced DataTools (www.advancedatatools.com) >> Blog: http://informix-myview.blogspot.com/ >> >> Disclaimer: Please keep in mind that my own opinions are my own opinions >> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any >> other organization with which I am associated either explicitly, >> implicitly, or by inference. Neither do those opinions reflect those of >> other individuals affiliated with any entity with which I am affiliated nor >> those of the entities themselves. >> >> >> >> >> On Tue, Nov 27, 2012 at 6:01 AM, nsimba toni <ntoni.nsimba@esw-gmbh.de>wrote: >> >>> I have a field in a table. This field is ar2.wbzdatum and is of type >>> number. >>> For example 90812. I want to convert this field in date format. the >>> result must be the date 09.08.12. how can I make my SQL statement. What >>> function I have to use. I have written so. >>> >>> SELECT Date(ar2.wbzdatum) as test >>> this ist not correct. >>> >>> Thank you >>> _______________________________________________ >>> Informix-list mailing list >>> Informix-list@iiug.org >>> http://www.iiug.org/mailman/listinfo/informix-list >>> >> >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> >> > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > >