Need help running query on data/time field

[Techhead]

I have a date/time field with a sql format of "datetime" The actual
date/time data format is MM/DD/YYYY^hh:mm:ss:p m or "1/25/2007
12:00:16 AM" Both the date and time are combined on the same field
with a space seperating the two.

I need to run a query on this date/time field using the criteria of
today's date so if I want to search all records with todays date, I
need to query on this field. I don't need the time, but just today's
date (MM/DD/YYYY) portion of the field.

Thanks,
Brian

[Plamen Ratchev]

You can return all records with today's date using a WHERE condition like
this:

WHERE mydate >= DATEDIFF(day, 0, getdate())
AND mydate < DATEDIFF(day, 0, getdate() + 1)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Techhead]

On Feb 8, 12:41 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

You can return all records with today's date using a WHERE condition like
this:

WHERE mydate >= DATEDIFF(day, 0, getdate())
AND mydate < DATEDIFF(day, 0, getdate() + 1)

HTH,

Plamen Ratchevhttp://www.SQLStudio.c om

Awesome! Worked perfect!

[Techhead]

On Feb 8, 12:57 pm, "Techhead" <jorgenso...@gm ail.comwrote:

On Feb 8, 12:41 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

You can return all records with today's date using a WHERE condition like
this:

WHERE mydate >= DATEDIFF(day, 0, getdate())
AND mydate < DATEDIFF(day, 0, getdate() + 1)

HTH,

Plamen Ratchevhttp://www.SQLStudio.c om

Awesome! Worked perfect!

Now how can I get a SUM of all records returned by this query. I know
SELECT SUM(*) AS TOTAL FROM does not work. What else can I try?

[Plamen Ratchev]

You have to use COUNT for number of records, not SUM. You can use SUM to
summarize a value if needed. Here is an example:

CREATE TABLE #Test(mydate datetime, myvalue int)

INSERT INTO #Test VALUES(DATEADD( hour, 2, getdate()), 2)
INSERT INTO #Test VALUES(DATEADD( hour, 3, getdate()), 3)
INSERT INTO #Test VALUES(DATEADD( hour, 4, getdate()), 4)

SELECT COUNT(*) AS counts, SUM(myvalue) AS total
FROM #Test
WHERE mydate >= DATEDIFF(day, 0, getdate())
AND mydate < DATEDIFF(day, 0, getdate() + 1)

DROP TABLE #Test

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Techhead]

On Feb 8, 1:51 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

You have to use COUNT for number of records, not SUM. You can use SUM to
summarize a value if needed. Here is an example:

CREATE TABLE #Test(mydate datetime, myvalue int)

INSERT INTO #Test VALUES(DATEADD( hour, 2, getdate()), 2)
INSERT INTO #Test VALUES(DATEADD( hour, 3, getdate()), 3)
INSERT INTO #Test VALUES(DATEADD( hour, 4, getdate()), 4)

SELECT COUNT(*) AS counts, SUM(myvalue) AS total
FROM #Test
WHERE mydate >= DATEDIFF(day, 0, getdate())
AND mydate < DATEDIFF(day, 0, getdate() + 1)

DROP TABLE #Test

Regards,

Plamen Ratchevhttp://www.SQLStudio.c om

Thank you. COUNT was what I was looking for... sorry. Can I take this
one step further? I need to subtract the COUNT results of 1 query from
the COUNT results of another query.

Here are my 2 queries:

SELECT COUNT (*) FROM TABLE.RECORDS WHERE DATEFIELD >= DATEDIFF(day,
0, getdate()) AND DATEFIELD < DATEDIFF(day, 0, getdate() + 1)AND
RECORD_TYPE = '1'

SELECT COUNT (*) FROM TABLE.RECORDS WHERE DATEFIELD >= DATEDIFF(day,
0, getdate()) AND DATEFIELD < DATEDIFF(day, 0, getdate() + 1)AND
RECORD_TYPE = '2'

I need to subtract the results from query 2 from query 1

Once I get this, I am set.

[Plamen Ratchev]

Here are two ways to do that:

SELECT SUM(CASE WHEN RECORD_TYPE = '1' THEN 1 WHEN RECORD_TYPE = '2' THEN -1
ELSE 0 END) AS CountsDiff
FROM TABLE.RECORDS
WHERE DATEFIELD >= DATEDIFF(day, 0, getdate())
AND DATEFIELD < DATEDIFF(day, 0, getdate() + 1)
SELECT count1 - count2 AS CountsDiff
FROM (SELECT COUNT(*) AS count1
FROM TABLE.RECORDS
WHERE DATEFIELD >= DATEDIFF(day, 0, getdate()) AND DATEFIELD <
DATEDIFF(day, 0, getdate() + 1)
AND RECORD_TYPE = '1') AS C1,
(SELECT COUNT(*) AS count2
FROM TABLE.RECORDS
WHERE DATEFIELD >= DATEDIFF(day, 0, getdate()) AND DATEFIELD <
DATEDIFF(day, 0, getdate() + 1)
AND RECORD_TYPE = '2') AS C2

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

Techhead (jo*********@gm ail.com) writes:

I have a date/time field with a sql format of "datetime" The actual
date/time data format is MM/DD/YYYY^hh:mm:ss:p m or "1/25/2007
12:00:16 AM" Both the date and time are combined on the same field
with a space seperating the two.

Actually, the format datetime columns is binary, it is not a string.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx