How to get DateFormula data from SQL query

Business Central/NAV Technical Forum
1

Please, advise how I could get the DateFormula field value from NAV using SQL query.
I have the ‘Lead Time Calculation’ field in the Item table and I’d like to get the value as it’s entered there, e.g. 12W I’d like to get as 12W, but it returns 12 with some special character and the CAST or other functions seem as not helping on it.
Please, advise what SQL fuction if any I could use to get it correctly.

Thanks

2

Hi and welcome to forum!

This is not so easy as it seems to be, because the letters in DateFormula change depending on language.
Thats why there is this special character, which is replaced with corresponding letter according to interface language. In my localisation 12 weeks is 12N, not 12W, for example…

3

Thanks, Modris. So, is there any way to get it from the SQL query? Please, advise.

4

I think you would have to do your own conversion of the special character to your language specific character. I would just enter each date formula one by one, selecting it from the database after each change. That way you can determine the special characters and convert them in your select statement.

5

Probably not very elegant, but should work. To get the translations create a new table with 2 fields in it, 1 DateFormula, 2 Text. Insert your values in there in both fields and run a SELECT * on it from SQL, you can now see what your “translation” should be in the program that reads your DateFormula.

6

Thank you so much, SNielsen.

It’s working perfectly.

7

How could I mark it as a solution?

8

Hmmmm… Verify button has dissapeared in SQL forum’s all threads, not only this particular one… I’ll report this to Admins.