Re: SQL convert number into the date

Using substr is hard because of the missing lead zero's, but I just posted a method usint to_char() to fill in the missing lead zero which will permit direct casting to a DATE. 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 9:19 AM, Fernando Nunes <domusonline@gmail.com>wrote: > 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... >