CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005sql-server-2008-r2
Adalarasan_Serangulam
Adalarasan_Serangulam

Reputation: 728

How to display the date as mm/dd/yyyy hh:mm Am/PM using sql server 2008 r2?

My sample query is 

SELECT D30.SPGD30_LAST_TOUCH_Y
from CSPGD30_TRACKING D30

My given date format is like "2013-01-01 00:00:00.000". I need to convert this date format to "mm/dd/yyyy hh:mm AM/PM". Do you have any idea about that?

Upvotes: 26

Views: 145572

Answers (10)

subash sapkota
subash sapkota

Reputation: 81

You can use this query which will give AM and PM as well. SELECT FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss tt') AS requestedDateFormat;

Upvotes: 0

Bayron Perdomo
Bayron Perdomo

Reputation: 11

I realize this is a 8 year old question but it was answered in many ways and none are simple enough. This is what I found to be simple and matches just about what the user is asking for (year is two digits, and seconds are present), assuming that the date he is getting is from GETDATE(), not that it matters but that is where my answer comes from.

SELECT CONVERT(varchar, D30.SPGD30_LAST_TOUCH_Y, 22) AS [DateTime]

12/16/20 10:19:18 AM

Upvotes: 0

RKS
RKS

Reputation: 1

Use this

Select (Convert(Varchar,GetDate(),101))+' '+(Right(('0'+(LTrim((Left((Right((Convert(Varchar,GetDate(),100)),7)),5))))),5))+' '+(Right((Convert(Varchar,GetDate(),100)),2))

Upvotes: 0

KateA
KateA

Reputation: 701

The FORMAT() function is available from version 2012 onwards. Once upgraded, you can use 

select FORMAT(@date,'MM/dd/yyyy hh:mm:s tt')

Upvotes: 41

Kaf
Kaf

Reputation: 33829

I think there is no single format to give them both. Try this using Convert; Sql-Demo

declare @mydate datetime = getdate()
select convert(varchar(10),@mydate, 101) + right(convert(varchar(32),@mydate,100),8)

|           COLUMN_0 |
----------------------
| 02/22/2013  9:36AM |

Upvotes: 42

Charan Ghate
Charan Ghate

Reputation: 27

Use the following scenario for getting date,time,day,month,year,hours,minutes,seconds,AM/PM

:)

SELECT UpdatedOn ,
  CONVERT(varchar,UpdatedOn,100) DateTime,
  CONVERT(varchar,UpdatedOn,10) Date ,
  CONVERT(varchar,UpdatedOn,108) Time ,
  substring(CONVERT(varchar,UpdatedOn,106),1,2) Day,
  substring(CONVERT(varchar,UpdatedOn,106),4,3) CMonth,
  substring(CONVERT(varchar,UpdatedOn,105),4,2) NMonth,
  substring(CONVERT(varchar,UpdatedOn,106),8,4) Year,
  left(right(CONVERT(varchar,UpdatedOn,100),7),2) Hours_12,
  substring(CONVERT(varchar,UpdatedOn,108),1,2) Hours_24,
  substring(CONVERT(varchar,UpdatedOn,108),4,2) Minutes,
  substring(CONVERT(varchar,UpdatedOn,108),7,2) Second,
  right(CONVERT(varchar,UpdatedOn,100),2) AM_PM 
FROM dbo.DeviceAssignSim 
WHERE AssignSimId=55;

Upvotes: 1

Meherzad
Meherzad

Reputation: 8553

Try this

 SELECT convert(varchar(20), GetDate(), 0);

To extract only AM/PM

substring(convert(varchar(30), GetDate(), 9), 25, 2);

Fiddle

Upvotes: 1

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

Use this

select CONVERT(VARCHAR(10), mydate, 101) + ' ' + RIGHT(CONVERT(VARCHAR, mydate, 100), 7) from tablename

Upvotes: 1

Guffa
Guffa

Reputation: 700422

Use the convert method to format a datetime value. Example:

select convert(varchar(20), D30.SPGD30_LAST_TOUCH_Y, 101)

The third parameter determines the format. You can find the available formats in the cast and convert documentation.

Upvotes: 0

user1848942
user1848942

Reputation: 355

You can do it like this:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

For more information look this:

date-format

Upvotes: 0

Related Questions