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?

Solution 1

SELECT DATEPART(HOUR, GETDATE());

DATEPART documentation

Solution 2

... 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")

Solution 3

Use datepart.

E.g.:

datepart(hh, date)

Solution 4

try this one too:

   DATEPART(HOUR,GETDATE()) 

Solution 5

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

View more solutions

Comments

[Tarzan]

I find this useful: SELECT FORMAT(getdate(), 'hh:mm:ss', 'en-US') 'Time'

[Adir D]

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 blogs.sqlsentry.com/aaronbertrand/… and sqlblog.com/blogs/aaron_bertrand/archive/2011/09/20/…

[Cindy Meister]

What's the point of your contribution, which repeats information provided in almost every other "answer" to this question?

[Auspex]

@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.

[Adir D]

@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.

[Auspex]

@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.

[Nico Haase]

Please add some explanation to your answer such that others can learn from it