By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Need help running query on data/time field

P: n/a
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:pm 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

Feb 8 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Feb 8 '07 #2

P: n/a
On Feb 8, 12:41 pm, "Plamen Ratchev" <Pla...@SQLStudio.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.com
Awesome! Worked perfect!

Feb 8 '07 #3

P: n/a
On Feb 8, 12:57 pm, "Techhead" <jorgenso...@gmail.comwrote:
On Feb 8, 12:41 pm, "Plamen Ratchev" <Pla...@SQLStudio.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.com

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?

Feb 8 '07 #4

P: n/a
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
Feb 8 '07 #5

P: n/a
On Feb 8, 1:51 pm, "Plamen Ratchev" <Pla...@SQLStudio.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.com
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.




Feb 8 '07 #6

P: n/a
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

Feb 8 '07 #7

P: n/a
Techhead (jo*********@gmail.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:pm 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****@sommarskog.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
Feb 8 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.