DATEPART problem

Question

I am trying to compare a value to the current hour of the day.  SQL Server is seeing DATEPART as a column and trying to compare "DATEPART" instead of using the function.  Any suggestions?

Thanks

SELECT DATE(DATE_TIME) as TheDate,
SUBSTRING(DATE_TIME,12,2) as TheHour,
count(*) as TheCount
FROM PG_ACTIVE_ORD_LOG 
 WHERE TRANS_TYPE IN ('ASO','AMO') AND
DATE(DATE_TIME) >= today AND
TheHour = (HOUR(GetDate()))
group by TheHour,TheDate

Answer 1

It is not a T-SQL code. What database you're using?

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

Garmon,

I don't see DATEPART used in your TSQL code, but please refer to the msdn article that shows it's myriad of uses HERE.

Hope this helps,
Sean

Answer 3

Sorry, I pasted the wrong version.  I have been trying several different things.  I am honestly not sure what the database type is.  I am using KBSQL ODBC drivers to get to it.

SELECT DATE(DATE_TIME) as TheDate,
SUBSTRING(DATE_TIME,12,2) as TheHour,
count(*) as TheCount
FROM PG_ACTIVE_ORD_LOG 
 WHERE TRANS_TYPE IN ('ASO','AMO') AND
DATE(DATE_TIME) >= today AND
TheHour = DATEPART(HOUR(GetDate()))
group by TheHour,TheDate

Answer 4

The reason I said it's not T-SQL code because there is no built-in DATE function in it.

Anyway, try your exact query, but replace TheHour with the SUBSTRING(...) everythere you use it - you can only use computed column in the ORDER BY clause, but not in GROUP BY or WHERE clause.

I also assume that you store your Date_Time field as character (or varchar) - otherwise you can not use SUBSTRING either.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 5

I know why the optimizer is confused because I am having a hard time discerning which is a column and which is a function call myself :^).  First date is not a valid function in SQL Server.  If you want to compare time values you are much better off leaving dates in datetime format and using a range >= and <.

Try this instead

SELECT
    TheDate,
    TheHour,
    COUNT(*) AS TheCount
FROM
    PG_ACTIVE_ORD_LOG
WHERE
    TRANS_TYPE IN ('ASO','AMO')
    AND TheDate >= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
    AND TheHour = DATEPART(HOUR,GETDATE())
GROUP BY
    TheHour,
    TheDate

---

http://jahaines.blogspot.com/