Extracting hour value from datetime column using DATEPART function

Question

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;

Icarus · Accepted Answer · 2016-06-22T00:36:37.727

3

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 ?

edited Jun 22 '16 at 00:36

answered Mar 14 '12 at 19:21

Icarus

63,293
14
100
115

Thank you. I don't know how I overlooked the quotes in the documentation. Regarding the WHERE statement, both versions work. I don't know if one is better. – hughesdan Mar 14 '12 at 19:36
Yes I get the same query results if I use <> null – hughesdan Mar 14 '12 at 19:40
1

It's been a few years, so hopefully you're using "is not null" instead of "<> null" by now. Reference: [How to Get NULLs Horribly Wrong](https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/) – RobertB Jun 21 '16 at 21:49

score 0 · Answer 2 · answered Aug 15 '18 at 08:33

There are several ways you can extract only the number of hours from an MS Access Date value in a query expression a few ways.

Here are some of the methods:

DatePart("h", [yourFieldName])  'returns a variant (Integer)

Format([yourFieldName],"h")     'returns a variant (String)

Hour([yourFieldName])           'returns a variant (Integer)

In Access, I prefer the last method because it's short and clear.

Based on the question, I suspect you were looking at the documentation for the SQL Server DATEPART function which has some subtle differences.

Don't make the mistake of thinking that SQL is all the same. There are similarities but the syntax is not always interchangeable, especially when talking about MS Access. When searching for answers about Access, I always include mc access as part of my Google search query, and even still, when you find a potential answer, confirm that the page you're looking at was actually written for Access.

Your NULL problem

There's something you didn't ask for help for, but that you need help with! :) Indeed this is an old post, so you must have either figured out your mistake, or you're still stuck on it, or the company has been putting out incorrect reports for the last 6 years.

[myFieldName] Is Not Null is not the same as [myFieldName] <> Null.

The short explanation is that Null is not a value. Null can things around it. It's not a zero, it's not a '' empty string, and it's not a ZLS (zero-length-string).

The way you were using the <> NULL criteria it's likely your query was returning no records because of the incorrect syntax.

Examples:

For example: what result do you think this simple equation will produce?

(Null+2)*3

...if you said 6, you're wrong!

The result is: NULL. Basically, anything that touches NULL becomes NULL.

One more example. Run this in VBA:

 If Null = Null Then MsgBox "Null equals Null." Else MsgBox "Null does NOT equal Null."

...you might not get the answer you'd expect!

For accuracy, all operators must be used the way they were intended, including the use of Is and Is Not operators with the NULL Statement.

More Information:

Office Support : DatePart Function (Access Query or VBA)
Office Support : Format Function (Access Query or VBA)
Office Support : Hour Function (Access Query or VBA)
Allen Browne : Common Errors with Null
Red-Gate : How to get NULLs Terribly Wrong in SQL Server
TechRepublic : 10 tricks for handling Null values in Microsoft Access
Wikipedia : Null (mathematics)
MSDN: Is Operator

Preparing for Unexpected Data:

This is actually only barely related, but made me LMAO because I never woudl have thought to prepare for this, and I figured I ought to share:

...which, in return, reminds me of that annoying little kid, Bobby Tables.

score 0 · Answer 3 · answered Mar 14 '12 at 19:22

0

You can use HOUR function instead of DATEPART.It gives hour numbers irrespective of AM/PM

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

answered Mar 14 '12 at 19:22

Teja

13,214
36
93
155

Thanks. I didn't know about HOUR. I'm not sure whether to select your answer or @Icarus. Both get the job done. I'll leave the question open for a bit to see of the votes indicate whether one approach is better. – hughesdan Mar 14 '12 at 19:39
DatePart("h", "01/05/2012 9:54 PM") returns 21 and not 9. – JeffO Mar 15 '12 at 00:14

Extracting hour value from datetime column using DATEPART function

3 Answers3

Your NULL problem

Examples:

More Information:

Preparing for Unexpected Data: