SQL convert number into the date
A user had a numeric column (e.g. 90812) holding dates in MMDDYY form and wanted it as a real DATE; SELECT DATE(col) failed. Replies explained there's no direct cast, partly because single-digit months lack a leading zero, and suggested: adding a new DATE column and filling it via a stored function using SUBSTR/TO_DATE or MDY(), then dropping/renaming the old column. Jason Harris posted an arithmetic one-liner using MDY() with TRUNC/MOD to split the number, and Art Kagel later offered TO_CHAR(col,"&&&&&&")::date. The poster never confirmed which worked, so no definitive resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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 >
I cannot understand the rule... It seems you may have one or two digits for the day and that seems dangerous. And two digits for the year reminds us of the Y2K problem... Anyway... You have the TO_DATE() function, and a combination of MDY() and SUBSTR().... Please check the SQL Syntax guide for specific information. Regards. On Tue, Nov 27, 2012 at 11: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 > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
Am Dienstag, 27. November 2012 12:24:27 UTC+1 schrieb Fernando Nunes: > I cannot understand the rule... It seems you may have one or two digits for the day and that seems dangerous. And two digits for the year reminds us of the Y2K problem... Anyway... You have the TO_DATE() function, and a combination of MDY() and SUBSTR().... Please check the SQL Syntax guide for specific information.Regards.On Tue, Nov 27, 2012 at 11: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 -- Fernando NunesPortugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... Am Dienstag, 27. November 2012 12:24:27 UTC+1 schrieb Fernando Nunes: > I cannot understand the rule... It seems you may have one or two digits for the day and that seems dangerous. And two digits for the year reminds us of the Y2K problem... Anyway... You have the TO_DATE() function, and a combination of MDY() and SUBSTR().... Please check the SQL Syntax guide for specific information.Regards.On Tue, Nov 27, 2012 at 11: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 -- Fernando NunesPortugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... Before I use the SUBSTR function. I need to first convert the field into a string. how does it work? Thank you
On Tuesday, 27 November 2012 19:01:52 UTC+8, nsimba toni 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. > Try: select mdy(trunc(ar2.wbzdatum / 10000), trunc(ar2.wbzdatum / 100)-(trunc(ar2.wbzdatum / 10000)*100), mod(ar2.wbzdatum, 100)+2000) HTH, Jason
OK, I have another idea:
alter table this_table add (new_date date not null);
update this_table set new_date = to_char( old_date, "&&&&&&" )::date;
alter table this_table drop (old_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 7:44 AM, nsimba toni <ntoni.nsimba@esw-gmbh.de>wrote:
> Am Dienstag, 27. November 2012 12:24:27 UTC+1 schrieb Fernando Nunes:
> > I cannot understand the rule... It seems you may have one or two digits
> for the day and that seems dangerous. And two digits for the year reminds
> us of the Y2K problem... Anyway... You have the TO_DATE() function, and a
> combination of MDY() and SUBSTR().... Please check the SQL Syntax guide for
> specific information.Regards.On Tue, Nov 27, 2012 at 11: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-- Fernando NunesPortugal
> http://informix-technology.blogspot.com My email works... but I don't
> check it frequently...
>
>
>
> Am Dienstag, 27. November 2012 12:24:27 UTC+1 schrieb Fernando Nunes:
> > I cannot understand the rule... It seems you may have one or two digits
> for the day and that seems dangerous. And two digits for the year reminds
> us of the Y2K problem... Anyway... You have the TO_DATE() function, and a
> combination of MDY() and SUBSTR().... Please check the SQL Syntax guide for
> specific information.Regards.On Tue, Nov 27, 2012 at 11: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-- Fernando NunesPortugal
> http://informix-technology.blogspot.com My email works... but I don't
> check it frequently...
>
> Before I use the SUBSTR function. I need to first convert the field into a
> string. how does it work?
>
> Thank you
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>