The date format is returned as mm / dd / yyyy hh: mm: ss AM / PM

Question

I am new to sql and my company just threw me into my head and said do it. Therefore, any help is greatly appreciated. I am trying to get a date to come out in the format mm / dd / yyyy hh: mm: ss AM / PM, for example, the date 09/26/2014 11:04:54 AM. I tried using the code:

Select Convert(nvarchar,EntryDate,101) From DB1 

However, this returns only 09/26/2014. I also tried

 Select Convert(nvarchar,EntryDate,100) From DB1 

but it returns September 26, 2014 11:04

Not sure where to go from here. Once again, thanks for the help. BTW I am using SQL Server 2012.

Answer 1

 DECLARE @Date_Value DATETIME = GETDATE(); SELECT CONVERT(VARCHAR(10), @Date_Value, 101) + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), @Date_Value, 22), 11)) RESULT: 09/26/2014 5:25:53 PM 

Your request

 SELECT CONVERT(VARCHAR(10), EntryDate, 101) + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), EntryDate, 22), 11)) From DB1 

Answer 2

Since you are working on SQL 2012, the format function should work:

 declare @date datetime = '2014-09-26 11:04:54' select FORMAT(@date,'MM/dd/yyyy hh:mm:s tt') 

Result: 09/26/2014 11:04:54 AM

In your case, it will be:

 Select FORMAT(EntryDate,'MM/dd/yyyy hh:mm:s tt') From DB1 

Answer 3

You must do this to convert the date in the format you are requesting for DATE_FORMAT (date,% m% d% Y% h:% i% p). Where date is the date you want to convert.

Hope this helps.

Answer 4

Try using the code snippet below.

 Select CONVERT(VARCHAR(11),EntryDate,101) From DB1 

I set the length in varchar.

Answer 5

USE two formats and combine them using the REPLACE and SUBSTRING functions.

  select CONVERT(nvarchar(128), dbo.GetDateTime(@input), 101) + REPLACE(SUBSTRING(CONVERT(nvarchar(128), dbo.GetDateTime(@input), 109), 12 , 128),':000', ' ') 

Answer 6

Combining two formats:

 select convert(char(11),getdate(),103) + stuff(right(convert(char(31),getdate(),130),14),9,4,' ') 

gives:

 26/09/2014 12:29:09 PM