Exporting SQLNAV 7.3.0.4294 to Excel has DATE format issue and shows 1/17/2018 12:00:00 A1P1 instead of 1/17/2018 12:00:00 AM

SQL Navigator
1

(I’m new and tried to locate a post on this before posting, but didn’t see any… sorry if this is a duplicate)

Exporting SQLNAV 7.3.0.4294 to Excel has bad DATE format

Format is “m/d/yyyy h:mm:ss \AmPm” but should be “m/d/yyyy h:mm:ss AM/PM” result is a value like “1/17/2018 12:00:00 AM” appears as “1/17/2018 12:00:00 A1P1”

I found mention of this on another forum where a competing product to SQL NAV also had the same problem, but I can’t find any mention for SQL NAV.

Thanks

David

2

I am having the same issue.

3

It appears that my SQL Nav is corrupt, but I'm not wanting to remove an reinstall at this time. My solution for the corrupt dates was to write an Excel macro to fix them. There is plenty info on the web on how to do this, so I'll keep this brief.

0 - you may be prompted to save this initial Workbook as an XLSB which enables the macro
1 - enable Developer tab in Excel
2 - record macro (give it meaningful name and pick your short cut)
IMPORTANT save it to "Personal Macro Notebook" to make it global
3 - do any simple task in Excel and then Stop Recording
4 - on Developer tab; click on Visual Basic to open it
5 - locate VBAProject(Personal.XLSB) and expand the Modules folder
6 - when you see your 'macro' code on the right panel; replace everything after the green comment area with the code below (the last thing in the green should be your shortcut key assignment)
7 - now in any export you may simply highlight the columns and then use your shortcut!

'
Dim rng As Range

For Each rng In Selection.Columns
   rng.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
Next rng

End Sub