How to Get Time Part from SQL Server 2005 Datetime in 'Hh:Mm Tt' Format

How to get time part from SQL Server 2005 datetime in 'HH:mm tt' format

One way is:

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

If you have a look at Books Online here, format 100 is the one that has the time element in the format you want it in, it's just a case of stripping off the date from the front.

How to get time part from GetDate() As hh:mm tt in SQL Server

You can use:

select CONVERT(VARCHAR(5), GETDATE(), 108) + ' ' + 
       RIGHT(CONVERT(VARCHAR(30), GETDATE(), 9),2)

SQL fiddle: http://sqlfiddle.com/#!6/a7540/2377

How to get Time from DateTime format in SQL?

SQL Server 2008:

SELECT cast(AttDate as time) [time]
FROM yourtable

Earlier versions:

SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable

Time part of a DateTime Field in SQL

In SQL Server if you need only the hh:mi, you can use:

DECLARE @datetime datetime

SELECT @datetime = GETDATE()

SELECT RIGHT('0'+CAST(DATEPART(hour, @datetime) as varchar(2)),2) + ':' +
       RIGHT('0'+CAST(DATEPART(minute, @datetime)as varchar(2)),2)

Convert to Datetime MM/dd/yyyy HH:mm:ss in Sql Server

Supported by SQL Server 2005 and later versions

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) 
       + ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)

* See Microsoft's documentation to understand what the 101 and 108 style codes above mean.

Supported by SQL Server 2012 and later versions

SELECT FORMAT(GETDATE() , 'MM/dd/yyyy HH:mm:ss')

Result

Both of the above methods will return:

10/16/2013 17:00:20

M/D/YYYY h:mm tt to DATETIME SQL Server

The string manipulation is being done to convert it into a recognised ISO standard format. Without doing that, a mistake could be made by doing CAST/CONVERT in confusing the days and months, since different cultures use different date formats. For example 12/2/2015 could represent December 2nd or 12th February.

The answer provided creates a string in the ISO format "YYYY-MM-DD HH:mm:ss". The first line after the SELECT extracts the year and appends a hyphen, the line after that does the month and hyphen, and so on. I'm assuming you spotted the part in that article where the OP pointed out the mistake in the accepted answer.

I assume you know what each individual function does (if you don't, you can search for the definitions on MSDN - do that now, or what follows won't make sense). However, it will probably help to explain some of the patterns being used in the solution when those functions are being used together.

CHARINDEX('/',REVERSE(notADateTime)) is looking at the string back-to-front and finding the first / (and since the string is backwards, that is the last /). So when used in conjunction with 'RIGHT', RIGHT(notADateTime,CHARINDEX('/',REVERSE(notADateTime))-1) is returning the right hand end of the string after the final /.

RIGHT('00'+*value*,2) is being used to ensure certain values (like hours, minutes, days and months) have two digits. The 2 means they want the final two characters (which will be digits, since this is a number). Note that '00' only needs to be '0', since any integer always has one digit already.

Once it's in that format wrap that in a CAST to DATETIME2, so it isn't a string any more.

Now you have this understanding you should be able to have a go at solving this yourself. Remember that solution expected seconds, which your strings don't have. Let me know if you need more help by adding a comment.

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 |

SQL Server Format Date DD.MM.YYYY HH:MM:SS

See http://msdn.microsoft.com/en-us/library/ms187928.aspx

You can concatenate it:

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) + ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)

---

Related Topics