Extracting hours from a DateTime (SQL Server 2005)

Question

I can extract the month and day by using Day(Date()), Month(Date()). I can't extract hours, with HOUR(Date()). I get the following error.

'HOUR' is not a recognized built-in function name.

How can I extract hours?

I find this useful: SELECT FORMAT(getdate(), 'hh:mm:ss', 'en-US') 'Time' — Tarzan, Apr 14 '21 at 12:28

score 472 · Accepted Answer · edited Oct 06 '14 at 19:57

472

SELECT DATEPART(HOUR, GETDATE());

DATEPART documentation

edited Oct 06 '14 at 19:57

Aaron Bertrand

272,866
37
466
490

answered Jul 11 '09 at 18:12

Dave Markle

95,573
20
147
170

15

Please spell out things like `HOUR` instead of using lazy shorthand that isn't always what you expect (try `y`). Feel free to maintain your own code however you like, but for teaching, I am opposed to ***promoting*** lazy shorthand that leads to confusion or worse. See #6 here http://blogs.sqlsentry.com/aaronbertrand/bad-habits-wrong-optimizations/ and https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx – Aaron Bertrand Oct 06 '14 at 19:44
11

@AaronBertrand That's not "lazy shorthand", it's the documented short-form. "Lazy" is using short-forms that are shorter than the minimal documented version, which may work but may have unintended consequences. – Auspex Jan 24 '19 at 10:51
6

@Auspex Can’t force you to not completely miss the point or to read the reasons behind my words. Not everything that’s documented is a best practice. – Aaron Bertrand Jan 24 '19 at 12:11
13

@AaronBertrand I got the point. But you're trying to enforce (to the extent of improperly editing _multiple_ people's answers) your own standards. Your "best practice" is nothing of the sort. – Auspex Jan 25 '19 at 13:05

score 46 · Answer 2 · answered Jan 22 '14 at 23:17

... you can use it on any granularity type i.e.:

DATEPART(YEAR, [date])

DATEPART(MONTH, [date]) 

DATEPART(DAY, [date])    

DATEPART(HOUR, [date]) 

DATEPART(MINUTE, [date])

(note: I like the [ ] around the date reserved word though. Of course that's in case your column with timestamp is labeled "date")

score 21 · Answer 3 · edited Jan 25 '19 at 17:46

21

Use datepart.

E.g.:

datepart(hh, date)

edited Jan 25 '19 at 17:46

Aaron Bertrand

272,866
37
466
490

answered Jul 11 '09 at 18:13

Adrian Godong

8,802
8
40
62

score 10 · Answer 4 · answered Jun 12 '12 at 15:26

10

try this one too:

   DATEPART(HOUR,GETDATE())

answered Jun 12 '12 at 15:26

score 7 · Answer 5 · edited Oct 04 '17 at 20:31

The DATEPART() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.

datepart    ***Abbreviation

year        ***yy, yyyy 
quarter     ***qq, q 
month       ***mm, m 
dayofyear   ***dy, y 
day         ***dd, d 
week        ***wk, ww 
weekday     ***dw, w 
hour        ***hh 
minute      ***mi, n 
second      ***ss, s 
millisecond ***ms 
microsecond ***mcs 
nanosecond  ***ns

Example

select * 
from table001
where datepart(hh,datetime) like 23

score 6 · Answer 6 · edited Aug 25 '17 at 16:44

DATEPART(HOUR, [date]) returns the hour in military time ( 00 to 23 ) If you want 1AM, 3PM etc, you need to case it out:

SELECT Run_Time_Hour =
CASE DATEPART(HOUR, R.date_schedule)
    WHEN 0 THEN  '12AM'
    WHEN 1 THEN   '1AM'
    WHEN 2 THEN   '2AM'
    WHEN 3 THEN   '3AM'
    WHEN 4 THEN   '4AM'
    WHEN 5 THEN   '5AM'
    WHEN 6 THEN   '6AM'
    WHEN 7 THEN   '7AM'
    WHEN 8 THEN   '8AM'
    WHEN 9 THEN   '9AM'
    WHEN 10 THEN '10AM'
    WHEN 11 THEN '11AM'
    WHEN 12 THEN '12PM'
    ELSE CONVERT(varchar, DATEPART(HOUR, R.date_schedule)-12) + 'PM'
END
FROM
    dbo.ARCHIVE_RUN_SCHEDULE R

score 4 · Answer 7 · edited Jan 23 '20 at 23:40

4

Try this one too:

SELECT CONVERT(CHAR(8),GETDATE(),108)

edited Jan 23 '20 at 23:40

Axifive

1,159
2
19
31

answered Jan 23 '20 at 23:00

armando rodriguez

41
2

score 1 · Answer 8 · edited Oct 04 '17 at 20:31

1

select case when [am or _pm] ='PM' and datepart(HOUR,time_received)<>12 
           then dateadd(hour,12,time_received) 
           else time_received 
       END 
from table

works

edited Oct 04 '17 at 20:31

C B

1,677
6
18
20

answered Sep 06 '17 at 21:27

Dani

11
1

score 0 · Answer 9 · answered Jul 12 '17 at 21:43

0

I can't extract hours, with HOUR(Date())

There is a way to call HOUR (I would not recommend to use it though because there is DATEPART function) using ODBC Scalar Functions:

SELECT {fn HOUR(GETDATE())} AS hour

LiveDemo

answered Jul 12 '17 at 21:43

Lukasz Szozda

162,964
23
234
275

score 0 · Answer 10 · answered Feb 19 '22 at 14:45

To include AM / PM - use the below:

SELECT 
  concat(case when datepart(hour,getdate()) % 12 = 0 then 12 
              else datepart(hour,getdate()) % 12 end,
         case when datepart(hour,getdate()) < 12 then ' AM' 
              else ' PM' end
        )

score -2 · Answer 11 · answered Jul 22 '20 at 10:52

-2

select convert(time,GETDATE())

answered Jul 22 '20 at 10:52

R.Alonso

989
1
8
9

10

Please add some explanation to your answer such that others can learn from it – Nico Haase Jul 22 '20 at 13:43

score -3 · Answer 12 · answered Apr 11 '16 at 05:27

-3

you must use datepart()

like 


datepart(hour , getdate())

answered Apr 11 '16 at 05:27

Fred

43
4

9

What's the point of your contribution, which repeats information provided in almost every other "answer" to this question? – Cindy Meister Apr 11 '16 at 06:00

Extracting hours from a DateTime (SQL Server 2005)

12 Answers12

Linked

Related