[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.axapta.programming

Date format in Axapta x++

FindOrCreateInventDim

12/3/2005 6:59:00 PM

Hi

I am a new to x++ programming, I have written a program in c# that generates
some x++ sql, now I wonder how a date should be formatted in x++ to work, the
two following statements give different results, the x++ statemen will not
find anything, the sql statement will work just fine.

I have also tried to send in the complete datetime format in the query
(hh:mm:sec) with no success, the date sent in is the value of the date
returned by Axapta without the date column in the where section.

Here are my statements.

X++, nothing found
SELECT * from %1
where %1.ACCOUNTCODE == 0
&& %1.ACCOUNTRELATION == '2423'
&& %1.ITEMCODE == 0
&& %1.FROMDATE == '2004-03-01'
&& %1.RELATION == 0
&& %1.MODULE == 2
&& %1.ITEMRELATION == '4660204'


SQL, rec found, this works fine
SELECT *
FROM PRICEDISCTABLE
WHERE (ACCOUNTCODE = 0)
AND (ACCOUNTRELATION = '2423')
AND (ITEMCODE = 0)
AND (FROMDATE = '2004-03-01')
AND (RELATION = 0)
AND ([MODULE] = 2)
AND (ITEMRELATION = '4660204')

This is driving me nuts, of course i understand that there must be some kind
of kernel date format to use, but what format, i can't find anything, any
suggestions are welcome.

Regards, from a ex Movex IRD guy
6 Answers

Daniel Grau

12/4/2005 3:47:00 PM

0

you should use dd\mm\yyyy

regards
daniel

"FindOrCreateInventDim" wrote:

> Hi
>
> I am a new to x++ programming, I have written a program in c# that generates
> some x++ sql, now I wonder how a date should be formatted in x++ to work, the
> two following statements give different results, the x++ statemen will not
> find anything, the sql statement will work just fine.
>
> I have also tried to send in the complete datetime format in the query
> (hh:mm:sec) with no success, the date sent in is the value of the date
> returned by Axapta without the date column in the where section.
>
> Here are my statements.
>
> X++, nothing found
> SELECT * from %1
> where %1.ACCOUNTCODE == 0
> && %1.ACCOUNTRELATION == '2423'
> && %1.ITEMCODE == 0
> && %1.FROMDATE == '2004-03-01'
> && %1.RELATION == 0
> && %1.MODULE == 2
> && %1.ITEMRELATION == '4660204'
>
>
> SQL, rec found, this works fine
> SELECT *
> FROM PRICEDISCTABLE
> WHERE (ACCOUNTCODE = 0)
> AND (ACCOUNTRELATION = '2423')
> AND (ITEMCODE = 0)
> AND (FROMDATE = '2004-03-01')
> AND (RELATION = 0)
> AND ([MODULE] = 2)
> AND (ITEMRELATION = '4660204')
>
> This is driving me nuts, of course i understand that there must be some kind
> of kernel date format to use, but what format, i can't find anything, any
> suggestions are welcome.
>
> Regards, from a ex Movex IRD guy

FindOrCreateInventDim

12/4/2005 5:27:00 PM

0

Hi Daniel, thanks for your quick reply, I have tried that date format too,
but it doesnt work.

I have written a small testprogram to test the statement and it dosn't work,
i am using c# and the com connector, my code is as follows

private void DateCheck()
{
IAxaptaRecord rec = m_axapta.CreateRecord(tabell.Text);
string statement = string.Empty;
statement = sql.Text;
//This will not find anything
/*SELECT * from %1
where %1.ACCOUNTCODE == 1
&& %1.ACCOUNTRELATION == '01'
&& %1.ITEMCODE == 0
&& %1.FROMDATE == '21/02/2001'
&& %1.RELATION == 4
&& %1.MODULE == 1
&& %1.ITEMRELATION == '10132428'*/

//This will find records
/*SELECT * from %1
where %1.ACCOUNTCODE == 1
&& %1.ACCOUNTRELATION == '01'
&& %1.ITEMCODE == 0
&& %1.RELATION == 4
&& %1.MODULE == 1
&& %1.ITEMRELATION == '10132428'*/

rec.ExecuteStmt(statement);
if(rec.Found == false)
{
MessageBox.Show("Nothing was found");
return;
}
while(rec.Found == true)
{
object dateStr = rec.get_field(kolumn.Text);
if (MessageBox.Show(this,dateStr.ToString(),"Found it,
continue",MessageBoxButtons.YesNo) == DialogResult.No)
{
return;
}
rec.Next();
}
}



--



"Daniel Grau" wrote:

> you should use dd\mm\yyyy
>
> regards
> daniel
>
> "FindOrCreateInventDim" wrote:
>
> > Hi
> >
> > I am a new to x++ programming, I have written a program in c# that generates
> > some x++ sql, now I wonder how a date should be formatted in x++ to work, the
> > two following statements give different results, the x++ statemen will not
> > find anything, the sql statement will work just fine.
> >
> > I have also tried to send in the complete datetime format in the query
> > (hh:mm:sec) with no success, the date sent in is the value of the date
> > returned by Axapta without the date column in the where section.
> >
> > Here are my statements.
> >
> > X++, nothing found
> > SELECT * from %1
> > where %1.ACCOUNTCODE == 0
> > && %1.ACCOUNTRELATION == '2423'
> > && %1.ITEMCODE == 0
> > && %1.FROMDATE == '2004-03-01'
> > && %1.RELATION == 0
> > && %1.MODULE == 2
> > && %1.ITEMRELATION == '4660204'
> >
> >
> > SQL, rec found, this works fine
> > SELECT *
> > FROM PRICEDISCTABLE
> > WHERE (ACCOUNTCODE = 0)
> > AND (ACCOUNTRELATION = '2423')
> > AND (ITEMCODE = 0)
> > AND (FROMDATE = '2004-03-01')
> > AND (RELATION = 0)
> > AND ([MODULE] = 2)
> > AND (ITEMRELATION = '4660204')
> >
> > This is driving me nuts, of course i understand that there must be some kind
> > of kernel date format to use, but what format, i can't find anything, any
> > suggestions are welcome.
> >
> > Regards, from a ex Movex IRD guy

rheu

12/5/2005 9:09:00 AM

0

Use function str2Date.
Syntax is str2Date(str text, int sequence).
The sequence of day (1), month (2) and year (3) in the date is indicated by
sequence.


"FindOrCreateInventDim" wrote:

> Hi
>
> I am a new to x++ programming, I have written a program in c# that generates
> some x++ sql, now I wonder how a date should be formatted in x++ to work, the
> two following statements give different results, the x++ statemen will not
> find anything, the sql statement will work just fine.
>
> I have also tried to send in the complete datetime format in the query
> (hh:mm:sec) with no success, the date sent in is the value of the date
> returned by Axapta without the date column in the where section.
>
> Here are my statements.
>
> X++, nothing found
> SELECT * from %1
> where %1.ACCOUNTCODE == 0
> && %1.ACCOUNTRELATION == '2423'
> && %1.ITEMCODE == 0
> && %1.FROMDATE == '2004-03-01'
> && %1.RELATION == 0
> && %1.MODULE == 2
> && %1.ITEMRELATION == '4660204'
>
>
> SQL, rec found, this works fine
> SELECT *
> FROM PRICEDISCTABLE
> WHERE (ACCOUNTCODE = 0)
> AND (ACCOUNTRELATION = '2423')
> AND (ITEMCODE = 0)
> AND (FROMDATE = '2004-03-01')
> AND (RELATION = 0)
> AND ([MODULE] = 2)
> AND (ITEMRELATION = '4660204')
>
> This is driving me nuts, of course i understand that there must be some kind
> of kernel date format to use, but what format, i can't find anything, any
> suggestions are welcome.
>
> Regards, from a ex Movex IRD guy

Mike Frank

12/5/2005 10:05:00 AM

0

What Daniel describes is the correct format to use. You should however write the date without
quotes. You could also use Global::date2StrXpp to get correctly formatted dates.

// the time part of DateTime will just be ignored
private string Date2StrXpp(DateTime date)
{
AxaptaParameterList paramList = new AxaptaParameterListClass();
paramList.Size = 1;
paramList.set_Element(1, date);

return axapta.CallStaticClassMethodEx("Global", "date2StrXpp", paramList) as string;
}

Mike

FindOrCreateInventDim

12/5/2005 1:01:00 PM

0

Hi Mike!

Thanks a lot !, it works perfectly now !, you saved me a lot of time !

--
Kind regards,
Hasse E, Cyncsis.


"Mike Frank" wrote:

> What Daniel describes is the correct format to use. You should however write the date without
> quotes. You could also use Global::date2StrXpp to get correctly formatted dates.
>
> // the time part of DateTime will just be ignored
> private string Date2StrXpp(DateTime date)
> {
> AxaptaParameterList paramList = new AxaptaParameterListClass();
> paramList.Size = 1;
> paramList.set_Element(1, date);
>
> return axapta.CallStaticClassMethodEx("Global", "date2StrXpp", paramList) as string;
> }
>
> Mike
>

FindOrCreateInventDim

12/5/2005 1:02:00 PM

0

Hi rehu,

Thanks a lot !, it works perfectly now !, you saved me a lot of time !

--
Kind regards,
Hasse E, Cyncsis.
--
Movex was fun, until java, now Axapta is fun.


"rheu" wrote:

> Use function str2Date.
> Syntax is str2Date(str text, int sequence).
> The sequence of day (1), month (2) and year (3) in the date is indicated by
> sequence.
>
>
> "FindOrCreateInventDim" wrote:
>
> > Hi
> >
> > I am a new to x++ programming, I have written a program in c# that generates
> > some x++ sql, now I wonder how a date should be formatted in x++ to work, the
> > two following statements give different results, the x++ statemen will not
> > find anything, the sql statement will work just fine.
> >
> > I have also tried to send in the complete datetime format in the query
> > (hh:mm:sec) with no success, the date sent in is the value of the date
> > returned by Axapta without the date column in the where section.
> >
> > Here are my statements.
> >
> > X++, nothing found
> > SELECT * from %1
> > where %1.ACCOUNTCODE == 0
> > && %1.ACCOUNTRELATION == '2423'
> > && %1.ITEMCODE == 0
> > && %1.FROMDATE == '2004-03-01'
> > && %1.RELATION == 0
> > && %1.MODULE == 2
> > && %1.ITEMRELATION == '4660204'
> >
> >
> > SQL, rec found, this works fine
> > SELECT *
> > FROM PRICEDISCTABLE
> > WHERE (ACCOUNTCODE = 0)
> > AND (ACCOUNTRELATION = '2423')
> > AND (ITEMCODE = 0)
> > AND (FROMDATE = '2004-03-01')
> > AND (RELATION = 0)
> > AND ([MODULE] = 2)
> > AND (ITEMRELATION = '4660204')
> >
> > This is driving me nuts, of course i understand that there must be some kind
> > of kernel date format to use, but what format, i can't find anything, any
> > suggestions are welcome.
> >
> > Regards, from a ex Movex IRD guy