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

Question

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?

Bounteous

Is your current column of datetime format? – Barrus

@ashreva, yes my current column is date time format. Do you have any idea about that. – Bounteous

Possible duplicate of #331502 – Sweatbox

@KasperVesth: what duplicate you find it ??. OP ask for datetime with AM/PM format. I dnt see AM/PM format in the link you provided – Eureetloir

@Satindersingh The post I linked to talks about changing the date time format in general by specifying what culture you are using. – Sweatbox

Answer 1

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 |

Vacancy

This does work @SHOHILSETHIA. What version of SQL server are you on? – Vacancy

Answer 2

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

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

Veronique

Not in SQL Server 2008 R2 - The Format function isn't defined. – Winy

Answer 3

Use this

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

Barrus

Answer 4

Try this

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

To extract only AM/PM

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

Fiddle

Lichfield

Answer 5

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;

Crossman

While this may answer the question, it's not a great solution due to it's lack of brevity. Other answers are both more concise and answer the question. – Leucas

there are simpler solution to this - how am I supposed to use it in a big query – Tantamount

Answer 6

You can do it like this:

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

For more information look this:

date-format

Shroud

if i use this format it shows only the "01/01/2013". In addtion to that i need the "01/01/2013 12:23 PM" – Bounteous

Answer 7

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.

Terrieterrier

thanks for your rpely, but it showing this format "01/01/2013" – Bounteous

@Adalarasan_Serangulam: Yes, check the documentation for the available formats. – Terrieterrier

Answer 8

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))

Diagnostic

Answer 9

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

Leisurely

On 2008R2, I just tested this and noted that replacing the source column (D30.SPGD30_LAST_TOUCH_Y above) with GETDATE() works. Replacing it with a date string causes the AM/PM to get cut off. Same thing happened for the accepted answer. Probably would need an additional convert to datetime for the source string to make it work. – Xylene

Answer 10

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;

Bijou