473,746 Members | 2,471 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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: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

Feb 8 '07 #1
7 7244
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...@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!

Feb 8 '07 #3
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?

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...@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.




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*********@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
Feb 8 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3056
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 to have an easier time understanding what I do. Therefore this weekend I'm going to spend 3 days just writing comments. Before I do it, I thought I'd ask other programmers what information they find useful. Below is a typical class I've...
19
4107
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 the code that implements managing unbound controls on forms given the superior performance of unbound controls in a client/server environment. I can easily understand a newbie using bound controls or someone with a tight deadline. I guess I need...
3
10660
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 that are hard to find. The main problem I am having right now is that I have a report that is sorted by one of these lookup fields and it only displays the record's ID number. When I add the source table to the query it makes several records...
11
2804
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 (strPubCity) for Publisher City. These two fields have a many-to-one relationship with tables, (tlkpPubName and tlkpPubCity) respectively. The lookup tables only have one field (strPubName and strPubCity), which is their primary key. I also have...
6
4846
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 SalesManName AT Alan Time
0
2212
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 server, which in turn has its tables linked to an Oracle back-end. I'm pretty sure I can take out Access as the middleman by just querying against the Oracle database, but that's not my question.
5
1708
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 user types in 08/07/06, the records of 08/05/06, 08/06/06 and 08/07/06 will all appear in the qry. I have the following code, which is real basic, but I am running into "the expression is typed incorrectly or is too complex etc".
2
2082
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 using data imported from an Excel file and the person entering into Excel only cared about the month and day portion and Excel added the current year to the field value. I want to produce a report that shows upcoming anniversaries using a parameter...
9
4021
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 my fields. When I try to run it I get #errors in my RunTot column. I'm kinda new to this. Not sure if maybe I mistyped something wrong or is there a better way to do this? I have pasted the code. Any help would be greatly appreciated....
0
8975
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9516
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9219
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6774
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4587
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4840
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3294
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2768
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2200
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.