Input Date fields on NAV based on MSSQL transactions

liizz

Hi all,

I have an issue when importing records from the MSSQL database to NAV.

I have 2 fields namely Start Date and End Date in my sql table.

On NAV, i want to input the Start Date and End Date on my card form which will display transactions made within that date range on the push action of command button('Import').

Please help

Thanks
Liizz

mohana_cse06

what problem are ypu facing here?

liizz

The filtering is not being done based on the Start Date and End Date on NAV, its importing the whole list of transactions.

Let's say:
Start Date:02/04/2012 ; End Date:06/04/2012.
I should have obtained data within this particular date range.

Thanks
Liizz

mohana_cse06

What did you write in Import button?

rhpnt

liizz wrote:

I have an issue when importing records from the MSSQL database to NAV.

If I understand this correctly you are importing some transactional data from the SQL system tables into a NAV database!? :shock:

liizz

Yes exactly..

But the BStartDate and BEndDate in sql is of datatype datetime.

On NAV, I want to input the date in the following format dd/mm/yy.

This is why I am having this issue about incorrect syntax near the year.

Please advise how to solve it out.

Thanks
Liizz

mohana_cse06

Check whether CONVERT Function helps you or not
http://www.w3schools.com/sql/func_convert.asp

liizz

Is there in NAV codes I can adjust the date format using EVALUATE.


Thanks
Liizz

bbrown

How is NAV "seeing" the data in the other database? Is this via a "linked table" or other method. This is where you want to handle any data format issues.

liizz

There is an application which is saving these transactional data in the SQL table and then an ADODB connection has been created to import these data into a temp table on NAV.

I do not want to change the datatype of the EndDate in SQL table.

It would been more appropriate to handle this change to dd/mm/yy in NAV itself.

bbrown

You don't need to change the datatype in the SQL table. It's already datetime. The same SQL datatype as the NAV database. What you need to do is format it in your SELECT statement using CAST/CONVERT.