hughesdan hughesdan - 5 months ago 17
SQL Question

Extracting hour value from datetime column using DATEPART function

I'm trying to extract the numerical hour value from a column that contains time formatted as DateTime. For example, here are a couple of records from that column:

Time Aired
4:20:00 PM
12:51:00 PM
3:17:00 PM
3:24:00 PM


From this column I'm trying to extract 4, 12, 3, 3, etc. The DATEPART function appears to be a good option for this. However, when I try to use that function as shown below I'm prompted to enter a parameter for Hour. This leads me to believe I'm implementing DATEPART incorrectly. Can someone spot what the problem may be? Could the problem be that my datetime formatted column contains time values only?

SELECT DATEPART(hour, [Time Aired]) AS Foo
FROM DRTV_CentralOnly
WHERE [Time Aired] <> null;

Answer

This documentation shows that the interval part (hours) should be enclosed in quotes and it should be h, not hours; therefore, try this:

SELECT DATEPART("h", [Time Aired]) AS Foo
FROM DRTV_CentralOnly
WHERE [Time Aired] is not null;

My other question would be, aren't you supposed to write WHERE [Time Aired] is not null ?