Get DateTime in mmddyyyy hh:mm format in SQL Server

pandeygolu4200
 
on Jul 11, 2022 04:19 AM
266 Views

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?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Andrea
 
on Jul 11, 2022 04:19 AM
Hi @pandeygolu4200,
Please try the following

Get ddyyyy Format from DateTime column in SQL Server

It might help you.

Cheers Andrea.