I have table where i store data in utc format, but in select query i want date in mm/dd/yyyy hh:mm format.
AuditId RoleId UserId ActionPerformed JsonObject Actiondate CreatedBy CreatedOn
0AAED5DC-292C-4CCF-8FD9-31826DC18475 584CA7D8-5CAE-4D11-A2B5-1082FB13930B 9B31094C-1AD6-43A2-9DBD-6A67F0AFE838 Login json 2022-07-06 07:31:51.287 NULL 2022-07-06 07:31:51.287
135C0649-DBF4-43E6-BAB4-4E49461E0BF0 796F5DDD-9CB0-4816-85D7-0A200486A6B7 D15D0932-17DE-41F4-BB8A-C9369920BF9D Log Out jsonobje 2022-07-06 07:31:51.287 NULL 2022-07-06 07:32:22.297
A1E1FCE4-F4EE-43A8-9C8E-897B239AFDCC 99AFB2DC-5DB7-4B7C-9538-85FF1B37D22E D15D0932-17DE-41F4-BB8A-C9369920BF9D Login NULL 2022-07-11 10:11:26.737 NULL 2022-07-08 10:47:28.497
SELECT
tblog.[AuditId]
,trol.RoleName AS [Role]
,ISNULL(tusers.FirstName,'') ++ ' ' + ISNULL(tusers.LastName,'') as [UserName]
,tblog.[ActionPerformed] AS [Action]
,tblog.[JsonObject]
,FORMAT (tblog.[Actiondate], 'dd/mm/yyyy hh:mm') AS [DateAndTime]
,tblog.[CreatedBy]
,tblog.[CreatedOn]
FROM [dbo].[tblAuditlogs] tblog WITH(NOLOCK)
INNER JOIN tblRoleMaster trol on tblog.[RoleId]=trol.RoleId
INNER JOIN tblUsers tusers on tblog.[UserId]=tusers.UserId
it is getting failed and generating below result.
AuditId Role UserName Action JsonObject DateAndTime CreatedBy CreatedOn
0AAED5DC-292C-4CCF-8FD9-31826DC18475 Admin Rahul Chavan Login json 06/31/2022 07:31 NULL 2022-07-06 07:31:51.287
135C0649-DBF4-43E6-BAB4-4E49461E0BF0 Staff Ravi Osp Log Out jsonobje 06/31/2022 07:31 NULL 2022-07-06 07:32:22.297
A1E1FCE4-F4EE-43A8-9C8E-897B239AFDCC SuperAdmin Ravi Osp Login NULL 11/11/2022 10:11 NULL 2022-07-08 10:47:28.497
how can i rectify this query?
