Re: SQL convert number into the date

I'd say something like this: MDY ( substr(column, 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...