Re: SQL convert number into the date

Probably the version you're using does not yet have SUBSTR. It should have SUBSTRING(), but the syntax is probably different. Please check the manual. Regards. On Wed, Nov 28, 2012 at 8: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... > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Am Mittwoch, 28. November 2012 11:28:52 UTC+1 schrieb Fernando Nunes: > Probably the version you're using does not yet have SUBSTR. It should have SUBSTRING(), but the syntax is probably different. Please check the manual. Regards. On Wed, Nov 28, 2012 at 8:54 AM, Ntoni Nsimba <ntoni....@esw-gmbh.de> wrote: I added the statement. my SQl statement is the followingSELECTar1.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 TestFROM fyar1sta ar1, fyar2sta ar2WHERE 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 4near character position 4 SUBSTR is a standard function . why is not found. thank you-----Ursprüngliche Nachricht----- Von: Fernando Nunes [mailto:domus...@gmail.com] Gesendet: Dienstag, 27. November 2012 15:25 An: Art KagelCc: Ntoni Nsimba; IIUG Informix List Betreff: Re: SQL convert number into the dateSorry 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....@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: Add a new DATE or DATETIME YEAR TO DAY type column 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. Use the function to populate the new column from the old one. Drop the old column. If required, rename the new column to the old column's name.Hmm alternatively, you could: expand the current column length to accommodate the date field separator characters, have the stored procedure return the properly formatted date string, use the function to update the current column with a formatted date, then alter the column type to DATE or DATETIME YEAR TO DAY instead. ArtArt 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....@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 testthis ist not correct.Thank you_______________________________________________ Informix-list mailing list Inform...@iiug.org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Inform...@iiug.org http://www.iiug.org/mailman/listinfo/informix-list -- Fernando Nunes Portugalhttp://informix-technology.blogspot.com My email works... but I don't check it frequently... -- Fernando NunesPortugalhttp://informix-technology.blogspot.com My email works... but I don't check it frequently... I think I have version problems because I have not the function SUBSTR , SUBSTRING, TO_CHAR, CHAR, ::, etc. I have the 7.25 version of IBM Informix SE. Please which version you have?

On Thu, Nov 29, 2012 at 4:11 AM, nsimba toni <ntoni.nsimba@esw-gmbh.de>wrote: > Am Mittwoch, 28. November 2012 11:28:52 UTC+1 schrieb Fernando Nunes: > > Probably the version you're using does not yet have SUBSTR. [...] > > I think I have version problems > because I have not the function SUBSTR , SUBSTRING, TO_CHAR, CHAR, ::, etc. > I have the 7.25 version of IBM Informix SE. > Please which version you have? > For better or worse, most people will assume you're using Informix (Dynamic Server) 11.x unless you specify a different server and version. SE 7.25 is in stasis; its SQL is essentially the SQL of Informix (OnLine and SE) from 1992, say, without VARCHAR support. It has stored procedures, but it doesn't have as many functions — and specifically does not have SUBSTR et al. The stored procedure I sent y'day (which, I've just seen, did not go to the list — oops; now it has) should work except... CREATE PROCEDURE ddmmyy_date(ddmmyy INTEGER) RETURNING DATE AS datevalue; you need to remove the 'AS datevalue' from the RETURNING clause. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."

Hi, What is wrong with this solution I posted days ago? Its pure maths! Jason select mdy(trunc(ar2.wbzdatum / 10000), trunc(ar2.wbzdatum / 100)-(trunc(ar2.wbzdatum / 10000)*100), mod(ar2.wbzdatum, 100)+2000

Nothing. More than one way to skin a cat. 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 Thu, Nov 29, 2012 at 8:16 PM, Jason Harris <jgh@jgharris.com> wrote: > Hi, > > What is wrong with this solution I posted days ago? Its pure maths! > > Jason > > select mdy(trunc(ar2.wbzdatum / 10000), trunc(ar2.wbzdatum / > 100)-(trunc(ar2.wbzdatum / 10000)*100), mod(ar2.wbzdatum, 100)+2000 >