Re: SQL convert number into the date

[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 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:domus...@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....@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....@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
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
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...

[Art Kagel]

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.

[nsimba toni]

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?

[Jonathan Leffler]

On Thu, Nov 29, 2012 at 4:11 AM, nsimba toni <ntoni....@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...@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."

[Jason Harris]

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

[Art Kagel]

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.