SQL Date Format Wrong

[crashff]

I have a report that I created using a date conversion on to display the date in an ODBC standard format YYYY-MM-DD. The sql command used is: Convert(nvarchar(10),tblAssetCustom.PurchaseDate,120) As [Purchase Date]. This works. This report was also created about v5152.

I have since upgraded us to v5166

I am trying to create a new report for disposal dates using Custom Field 5 as a Date selection, with the same YYYY-MM-DD format, using teh same command: Convert(nvarchar(10),tblAssetCustom1.Custom5,120) As [Disposal Date], and all I get is MM/DD/YYYY. I have tried using the entire list of known ODBC date styles, and none of them are displaying properly.

[crashff]

Thank you!

[Hemoco · Accepted solution]

Custom fields remain text fields within the Lansweeper database, regardless of what you set their display type to. This is why your conversion is failing, because you're not converting DateTime values. Convert your values to DateTime first, like so:

Convert(nvarchar(10),Convert(datetime,tblAssetCustom.Custom5),120)