How to Display the Date as Mm/Dd/Yyyy Hh:Mm Am/Pm Using SQL Server 2008 R2

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

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 |

Date format returned as mm/dd/yyyy hh:mm:ss AM/PM

Since you're 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 would be:

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

How do I format SQL Server 2008 Date in M/DD/YYYY h:mm am Format?

Try this:

declare @X datetime
set @X = '2012-01-25 05:24:05 pm' --this needs to show as 01/25/2012 5:24 pm

SELECT CONVERT(VARCHAR(10), @X, 101) + RIGHT(CONVERT(VARCHAR(100), @X, 100),CHARINDEX (' ',REVERSE(CONVERT(VARCHAR(100), @X, 100))))

Result:

01/25/2012  5:24PM

Remark: There is missing space between AM/PM and minutes. If it is improtant use this:

declare @X datetime
declare @stringX varchar(100)
set @X = '2012-01-25 05:24:05 pm' --this needs to show as 01/25/2012 5:24 pm

SELECT @stringX = CONVERT(VARCHAR(10), @X, 101) + RIGHT(CONVERT(VARCHAR(100), @X, 100),CHARINDEX (' ',REVERSE(CONVERT(VARCHAR(100), @X, 100))))

SELECT LEFT (@stringX, LEN(@stringX)-2) + ' ' + LOWER(RIGHT(@stringX, 2))

Result:

01/25/2012 5:24 pm

sql server convert date to string MM/DD/YYYY

That task should be done by the next layer up in your software stack. SQL is a data repository, not a presentation system

You can do it with

CONVERT(VARCHAR(10), fmdate(), 101)

But you shouldn't

How do I format date and time on ssrs report?

=Format(Now(), "MM/dd/yyyy hh:mm tt")

Output:

04/12/2013 05:09 PM

Full list of format options might be found here. Kudos to @MattGibson.

How to Convert datetime value to yyyymmddhhmmss in SQL server?

Since SQL Server Version 2012 you can use:

SELECT format(getdate(),'yyyyMMddHHmmssffff')

Date format in dd/MM/yyyy hh:mm:ss

This can be done as follows :

select CONVERT(VARCHAR(10), GETDATE(), 103) + ' '  + convert(VARCHAR(8), GETDATE(), 14)

Hope it helps

How to Convert DATETIME to CHAR in MS SQL

 SELECT CONVERT(NVARCHAR,GETDATE(),101) + ' ' + 
     CASE SUBSTRING(CONVERT(NVARCHAR,GETDATE(),100),13,1) WHEN ' ' THEN '0' ELSE SUBSTRING(CONVERT(NVARCHAR,GETDATE(),100),13,1) END +
     SUBSTRING(CONVERT(NVARCHAR,GETDATE(),100),14,4) + ' ' + RIGHT(CONVERT(NVARCHAR,GETDATE(),100),2)

Might I also suggest this code:

 DECLARE @OFDate DATETIME

 SET @OFDate = DATEADD(hh,13,GETDATE())

 SELECT CONVERT(NVARCHAR,@OFDate,101) + ' ' + 
 CASE SUBSTRING(CONVERT(NVARCHAR,@OFDate,100),13,1) WHEN ' ' THEN '0' ELSE SUBSTRING(CONVERT(NVARCHAR,@OFDate,100),13,1) END +
 SUBSTRING(CONVERT(NVARCHAR,@OFDate,100),14,4) + ' ' + RIGHT(CONVERT(NVARCHAR,@OFDate,100),2)

which you can use to offset the current date to prove that it works for multiple cases. For instance, when I use the numbers 0, 1, 12 and 13 right now, I get:

04/18/2016 09:34 AM
04/18/2016 10:34 AM
04/18/2016 09:34 PM
04/18/2016 10:34 PM

which means you can probably guess my time zone.

This is pretty cumbersome code. I don't know if you can do any better or not, but it will hopefully get you started. I suggest that if you are going to be needing this in a lot of places, but without a whole lot of access in your procedure, that you could use a function to return it. If you're going to be doing it for lots of different lines in a table, though, you're better off just to put the unelegant, complicated code right into your procedure.

Convert a 12 hour format to 24 hour format in sql server

Try this:

First convert varchar date to datetime and then you can manipulate it in the way you want as:

-- CONVERT TO DATETIME TO GET 24HR FORMAT
SELECT CONVERT(DATETIME, '10/1/2013 6:39:04  PM', 0)  
-- Concatenate in required format
SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '10/1/2013 6:39:04  PM', 0), 101) 
+ ' '+ CONVERT(VARCHAR(5),CONVERT(DATETIME, '10/1/2013 6:39:04  PM', 0), 108)

---

Related Topics