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?

5

9 Answers

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 |

1

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

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

1

Use this

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

Try this

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

To extract only AM/PM

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

Fiddle

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;

2

You can do it like this:

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

For more information look this:

date-format

1

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.

2

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

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

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy