Input Date fields on NAV based on MSSQL transactions

liizz
liizz Member Posts: 125
edited 2012-04-12 in NAV/Navision Classic Client
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

Comments

  • mohana_cse06
    mohana_cse06 Member Posts: 5,506
    what problem are ypu facing here?

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • liizz
    liizz Member Posts: 125
    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
    mohana_cse06 Member Posts: 5,506
    What did you write in Import button?

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • rhpnt
    rhpnt Member Posts: 688
    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
    liizz Member Posts: 125
    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
    mohana_cse06 Member Posts: 5,506
    Check whether CONVERT Function helps you or not
    http://www.w3schools.com/sql/func_convert.asp

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • liizz
    liizz Member Posts: 125
    Is there in NAV codes I can adjust the date format using EVALUATE.


    Thanks
    Liizz
  • bbrown
    bbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • liizz
    liizz Member Posts: 125
    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
    bbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.

Categories