Help! - Query On Time Field In SQL Server - How?

[compusup3000@yahoo.com]

Hi,

I have an orders database and I need to be able to write a query
that groups sales by:

1. Date
2. Time of day (both am and pm)

I currently have a date/time field named "Submitted" that stores
just the date.

However, how do I store the time?

I know I can store the date/time in the "Submitted" field I
created. However, because of the time being stored in that field, I
can't execute an easy query such as:

select * from database where submitted = '2/10/05'

(reason being, the time portion makes each "Submitted" field
record unique, so just the date won't be recognized)

I realize there probably is a much better way to do this in the
SQL query language that I am not aware of.

Two questions:

1. Based on what I'm trying to do -- query based on date and group
by number of sales for each hour of the day (am and pm) -- what is the
best approach to store the time?

2. What does the query look like that querys by date and groups
orders by time of day (am and pm)?

Thanks for your help.

J

[David Portas]

Re: Help! - Query On Time Field In SQL Server - How?

The examples below should help. Notice the Y-M-D formats I have used
for date literals. These formats are safe under any regional connection
settings whereas other formats used in code (such as in the example you
gave) may fail depending on the settings of DATEFORMAT and LANGUAGE.

CREATE TABLE YourTable (dt DATETIME PRIMARY KEY)

INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000 ')
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000 ')

/* Retrieve a particular date */
SELECT *
FROM YourTable
WHERE dt >= '20050210'
AND dt < '20050211'

/* Group by date and hour */
SELECT MIN(dt), COUNT(*)
FROM YourTable
GROUP BY DATEDIFF(HH,'20 000101',dt)

--
David Portas
SQL Server MVP
--