I have a view where I am casting a datetimeoffset(7) field to smalldatetime or datetime as my reporting application won't accept the datetimeoffset(7) format. Problem is I am trying to get this field to show up in UK format. I am using CAST to get my results. I can get the format I need by doing a CONVERT but my reporting system will not work with a varchar field and display the dates.
I am looking to get my results shown as dd-mm-yyyy hh:mm:ss or dd-mm-yyyy hh:mm:ss.ms
CAST statements:
CAST (dq.CreatedOn AS SMALLDATETIME) AS CreatedOn,
CAST (dq.CreatedOn AS DATETIME) AS CreatedOn2,
CONVERT statements: These won't work for my reporting system.
CONVERT(VARCHAR(30), (CAST (dq.CreatedOn AS SMALLDATETIME)), 29), -- 18-11-2025 10:21:00.000
CONVERT(VARCHAR(30), (CAST (dq.CreatedOn AS DATETIME)), 29), -- 18-11-2025 10:21:22.770
Any help would be appreciated.