473,238 Members | 1,862 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

Need help running query on data/time field

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
7 7200
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: lawrence | last post by:
I've been bad about documentation so far but I'm going to try to be better. I've mostly worked alone so I'm the only one, so far, who's suffered from my bad habits. But I'd like other programmers...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
11
by: my-wings | last post by:
I think I've painted myself into a corner, and I'm hoping someone can help me out. I have a table of books (tblBooks), which includes a field (strPubName) for Publisher Name and another field...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
0
by: schan | last post by:
Hi there, I was wondering if someone could shed some light on a problem I have no idea on how to fix. I created an Excel Add-In that uses an ADO connection to an Access database on a file...
5
by: vinfurnier | last post by:
Hi - I've been struggling to produce a working parameter query that will allow the end user to type in any date (mm/dd/yy) and obtain the records of the previous 2 days. In other words, if the...
2
by: Bill | last post by:
I have a 200 record database that includes a date/time field, AnnivDate, for a wedding anniversary. AnnivDate has nulls and some incorrect year data. I have been creating the Access database...
9
by: JJM0926 | last post by:
I'm trying to create a running totals query in access 97. I have followed the directions on how to do it from Microsofts website article id 138911. I took their code they had and replaced it with...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.