19

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

E.g. 

11:25 AM
14:36 PM
shA.t
  • 16,580
  • 5
  • 54
  • 111
Azhar
  • 20,500
  • 38
  • 146
  • 211

10 Answers10

26

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.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • 3
    This will print 8:00 PM, but I think the question is looking for 20:00 PM – Andomar Jul 08 '10 at 09:18
  • 3
    @Andomar - good point. If that is really what is needed, I'd argue the AM/PM is irrelevant and suggest a more conciser approach though – AdaTheDev Jul 08 '10 at 09:36
8

You'll need two converts, one to get the HH:mm time, and one to get AM/PM. For example:

declare @date datetime
set @date = '20:01'
SELECT CONVERT(VARCHAR(5), @date, 108) + ' ' +
       SUBSTRING(CONVERT(VARCHAR(19), @date, 100),18,2)

This prints:

20:01 PM

In a select query, replace @date with your column's name.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • I would replace the SubString() and Convert() with a single DatePart() and Case-Statement. This will also give you greater control over the casing/capitalization of your AM/PM. i.e. (CASE WHEN DATEPART(HOUR, GETDATE()) < 12 THEN 'am' ELSE 'pm' END) You could also include the space in ' am' and ' pm' so you have one less concatenation as well. – MikeTeeVee Jun 04 '13 at 20:42
7

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

Result

HourMinuteSecond: 13:06:56.5770000
DateOnly:         2012-07-26

SQL Server 2000/2005

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

http://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/

Tisho
  • 8,320
  • 6
  • 44
  • 52
Muhammad Waqas Iqbal
  • 3,244
  • 1
  • 20
  • 9
2

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

Result:

HourMinuteSecond: 13:06:56.5770000
DateOnly:         2012-07-26

SQL Server 2000/2005

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO
bytecode77
  • 14,163
  • 30
  • 110
  • 141
M.Mohammadi
  • 1,558
  • 1
  • 13
  • 25
2
select substring(CONVERT(VARCHAR, getdate(), 114),1,5)

resault : 22:05

Declan_K
  • 6,726
  • 2
  • 19
  • 30
test
  • 21
  • 1
1
select cast(getdate() as time)) [time],
Matt Cain
  • 5,638
  • 3
  • 36
  • 45
manisha Richhariya
  • 19
  • 1
1

This gives you an actual datetime and not varchar

CAST(LEFT(YOURDATETIME,12) AS SMALLDATETIME) AS YOURNEWDATE
Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
Frank C
  • 11
  • 1
1

You need to use CONVERT function:

CONVERT(VARCHAR, yourdatetimefiled, 114) AS [HH:MI(12H)]
Anax
  • 9,122
  • 5
  • 34
  • 68
0
select right(convert(char(20),getdate(),0),7)

No check though

Salil
  • 46,566
  • 21
  • 122
  • 156
0

For SQL Server 2012 and above use this:

SELECT Format(GetDate(), 'hh:mm tt')

YesOrYes
  • 13
  • 2