Format the date column with AM/PM and without second in SQL Server

At previous hint, I have explained How to convert Gregorian dates to Hijri dates format in SQL Server.

 At this hint, I will explain How to show the date field with data and time format with the following :

  • I need to format the time section to show AM/PM.
  • I need to show the time section without seconds.
dd/MM/yyyy HH:mm AM/PM

SQL_R4.PNG

Steps

  • Open a new query in SSMS > Drop the following query to show the data in Hijri format.
SELECT CONVERT(VARCHAR(30),GETDATE(),131) AS DateConvert;

Output

SQL_R1

  • Format the date with only Hour and Minute via
select convert(nvarchar(16),GetDate(),131)

Output(Query 1)

SQL_R2

  • Get only AM or PM
Select substring(convert(varchar(30), GetDate(), 9), 25, 2)

Output(Query 2)

SQL_R3

Concatenate Query 1 with Query 2 to get the required format.

select convert(nvarchar(16),GetDate(),131) 
+ ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

Output

SQL_R4.PNG

To get only HH:mm AM/PM format, you would use the following query

SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5) 
+ ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

Output

SQL_R5

Enjoy 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s