Get Time from Getdate()

Question

I would like to take the Getdate() results of,

for example

2011-10-05 11:26:55.000

into

11:26:55 AM

I have looked other places and found

Select RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7)

which gives me

11:26AM

It's so close I can taste it!

Are you looking for a string representation in this specified format? — Martin Smith, Oct 05 '11 at 16:37
does the requirement dictate you not being able to fix this in code? — jim, Oct 05 '11 at 16:38
If you need this value as a TIME then I'd use the DATEDIFF answer from @Wil. If this really is about formatting strings, then I'd recommend you do that outside of SQL, such as in your client application. — MatBailie, Oct 05 '11 at 16:46
I will resign myself to this being a limitation of SQL (a silly formatting issue, but all the same) I will go ahead and do it in php. Thank you anyway — Newkidirus, Oct 05 '11 at 16:51

score 29 · Answer 1 · answered Oct 05 '11 at 16:37

29

select convert(varchar(10), GETDATE(), 108)

returned 17:36:56 when I ran it a few moments ago.

answered Oct 05 '11 at 16:37

Jamiec

133,658
13
134
193

Ah damn.. so close but no cigar. – Jamiec Oct 05 '11 at 16:39
That's better, but I require the AM/PM. – Newkidirus Oct 05 '11 at 16:40
1

else can use 'convert(time, getdate())' return that answer. – Ali U Apr 08 '14 at 13:53

score 11 · Answer 2 · edited May 23 '17 at 10:31

11

You might want to check out this old thread.

If you can omit AM/PM portion and using SQL Server 2008, you should go with the approach suggested here

To get the rid from nenoseconds in time(SQL Server 2008), do as below :

SELECT CONVERT(TIME(0),GETDATE()) AS HourMinuteSecond

I hope it helps!!

edited May 23 '17 at 10:31

Community

1
1

answered Oct 05 '11 at 16:56

Nilesh Thakkar

2,877
1
24
43

If you're converting it to varchar, it will no longer be datetime. Please check this [link](http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/format-date-sql-server.aspx) – Nilesh Thakkar Oct 05 '11 at 16:59

Marcelo · Answer 3 · 2019-09-04T01:23:48.103

6

Did you try to make a cast from date to time?

select cast(getdate() as time)

Reviewing the question, I saw the 'AM/PM' at end. So, my answer for this question is:

select format(getdate(), 'hh:mm:ss tt')

Run on Microsoft SQL Server 2012 and Later.

edited Sep 04 '19 at 01:23

answered Aug 29 '19 at 19:06

Marcelo

61
1
2

this is a comment and not an answer – Léa Gris Aug 29 '19 at 19:28
1

If you try to run this code you will see an answer and not a comment. – Marcelo Sep 04 '19 at 01:03
Thanks @Marcelo, forgot about the format function. – user70267 Nov 09 '21 at 16:36

score 3 · Answer 4 · answered Oct 05 '11 at 16:37

3

To get the format you want:

SELECT (substring(CONVERT(VARCHAR,GETDATE(),22),10,8) + ' ' + SUBSTRING(CONVERT(VARCHAR,getdate(),22), 19,2))

Why are you pulling this from sql?

answered Oct 05 '11 at 16:37

k.parnell

2,617
2
16
12

I hope to have a clock in php pulling time that is "live" but pulled from sql. (I'm using sqlsrv) – Newkidirus Oct 05 '11 at 16:41
1

You are much likely better off, from many perspectives, doing the formatting in PHP then. Let your SQL be about data and move the DATE and/or TIME values around as real values, not strings. Keeping your data and your presentation separated is very good form. – MatBailie Oct 05 '11 at 16:47
@Dems, I considered this, and can and have done this in the past, but I need it to come from my computer, not the computer viewing it. – Newkidirus Oct 05 '11 at 16:49
@Newkidirus you mean you need the time on the SQL box not the PHP box? – k.parnell Oct 05 '11 at 16:52
1

@Newkirirus - You mis-understand me. Use GetDate() to get the date from the SQL server by all means. But then format it as a string in PHP. SQL *can* format, but it isn't designed *for* formatting, it's for *data*. – MatBailie Oct 05 '11 at 17:44

Ardi · Answer 5 · 2019-12-06T03:23:23.660

1

Let's try this

select convert(varchar, getdate(), 108)

Just try a few moment ago

edited Dec 06 '19 at 03:23

answered Jun 14 '19 at 06:40

Ardi

65
7

score 0 · Answer 6 · edited Jul 16 '19 at 06:25

0

You will be able to get the time using below query:

select left((convert(time(0), GETDATE ())),5)

edited Jul 16 '19 at 06:25

Adrita Sharma

21,581
10
69
79

answered Jul 16 '19 at 06:14

Techniical Ravi

1
3

score 0 · Answer 7 · edited Mar 29 '20 at 07:20

0

You can use the datapart to maintain time date type and you can compare it to another time.
Check below example:

declare @fromtime time = '09:30'
declare @totime time
SET @totime=CONVERT(TIME, CONCAT(DATEPART(HOUR, GETDATE()),':', DATEPART(MINUTE, GETDATE())))
if @fromtime <= @totime 
begin print 'true' end
else begin print 'no' end

edited Mar 29 '20 at 07:20

סטנלי גרונן

2,917
23
46
68

answered Mar 29 '20 at 06:40

A.Ghandour

1
1

score 0 · Answer 8 · answered Oct 05 '11 at 16:35

0

If it's SQL Server 2005 there is no TIME datatype. The easiest way to get only the time component is to set the date to 1/1/1900.

DECLARE @time DATETIME

SET @Time = GETDATE()

SELECT DATEADD(dd,DATEDIFF(dd,@time,'1/1/1900'),@time)

answered Oct 05 '11 at 16:35

Wil

4,130
1
16
15

Get Time from Getdate()

8 Answers8

Linked