Hello Friends,
Happy New Year 2017.
This is my first blog for year 2017. Let wish this year will become more happy and healthy for all of us.
On Yesterday one of my customer has raise the ticket for following issue.
The Date is not valid. Page must be close.
I have identified root cause,
By using the third party tool or query someone has inserted invalid value in "Start Date" field.
Data Type of Start Date field is DATE but by force fully date time value updated into that field.
Example:
Starting Date has invalid value - 2016-12-29 13:03:39.090
Correct Value for Starting Date - 2016-12-29 00:00:00.000
See the following snapshot where you can find than starting date field contain invalid value in last 4th line.
SQL doesn't have only DATE data type. When create any date field in NAV that it will store as DateTime field SQL but in that case time value is always zero into that field
But if you update the time field from SQL side than it will error in NAV.
=================================================================
Select query to identified invalid value in Date field from table record.
Update query to update correct value in starting date field:
Happy New Year 2017.
This is my first blog for year 2017. Let wish this year will become more happy and healthy for all of us.
On Yesterday one of my customer has raise the ticket for following issue.
The Date is not valid. Page must be close.
I have identified root cause,
By using the third party tool or query someone has inserted invalid value in "Start Date" field.
Data Type of Start Date field is DATE but by force fully date time value updated into that field.
Example:
Starting Date has invalid value - 2016-12-29 13:03:39.090
Correct Value for Starting Date - 2016-12-29 00:00:00.000
See the following snapshot where you can find than starting date field contain invalid value in last 4th line.
SQL doesn't have only DATE data type. When create any date field in NAV that it will store as DateTime field SQL but in that case time value is always zero into that field
But if you update the time field from SQL side than it will error in NAV.
=================================================================
Select query to identified invalid value in Date field from table record.
SELECT
*
FROM
[****_LIVE_NAV_2013R2].[dbo].[**** - Live$Production Order]
Where cast([Starting
Date] as TIME) <> '00:00:00.000'
GO
Update query to update correct value in starting date field:
UPDATE [*****_LIVE_NAV_2013R2].[dbo].[***** - Live$Production Order]
SET
[Starting Date] = '2016-12-29 00:00:00.000'
WHERE
[No_] = '******/0000237'
GO
=================================================================
I hope it will help you some time.
Thank you reading.
Please write a comment if you face slimier issue with other fields.
Thank you.
Keep Sharing...Keep Growing...
