query for entries created 'today'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query for entries created 'today'

Author

Topic

ronsoto
Starting Member

5 Posts

Posted - 2009-09-30 : 16:10:39

hi.. well i'm a total newbie and am hoping that all you sql gurus can help out.

i need to search our db for new invoices and then make sure that the numbers match. what i need is just the date search portion. here's what i have so far (below). currently i just enter the date into the string that's already been created. The last 'like' equals a portion of the whole name = Principle. (i manually match the numbers by going into the system folder and counting them, then i would like to run the query below to see if the numbers match.

---
Select contact1.contact, contact1.company,contact1.lastname, contact1.department, contact2.uconfirmno from contact1,contact2 where contact1.accountno = contact2.accountno and contact1.key5 like '%9/29/%%09' and contact1.key4 like 'prin%'

---

I totally appreciate any help.

thanks,

ron

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-09-30 : 17:44:01

If the column 'key5' is not a datetime data type, you are going to have problems trying to query that table. If it is a datetime column, here is how I would modify that query:

DECLARE @filterDate datetime;
SET @filterDate = '20090929'; -- use unambiguous date

SELECT c1.contact
,c1.company
,c1.lastname
,c1.department
,c2.uconfirmno
FROM dbo.contact1 c1
INNER JOIN dbo.contact2 c2 ON c2.accountno = c1.accountno
WHERE c1.key5 >= @filterDate
AND c1.key5 < DATEADD(day, 1, @filterDate);

ronsoto
Starting Member

5 Posts

Posted - 2009-09-30 : 18:04:00

Hi Jeff.. and thanks for responding, I appreciate it.

Actually I don't care how it's written, I'm just looking to match today's date, against the inbound records received today, and then display those contact records. (the ones received today)

the query i have does work.. this said i have to manually change the sql query to reflect today's date, and the contact. (like='prin%')

Thanks,

Ron

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 01:57:02

use

SET @filterDate =dateadd(day,datediff(day,0,getdate()),0)

and

WHERE c1.key5 >=@filterDate
AND c1.key5 < DATEADD(day, 1, @filterDate);

Madhivanan

Failing to plan is Planning to fail

ronsoto
Starting Member

5 Posts

Posted - 2009-10-01 : 11:43:37

thanks madhivanan..

as a noob, i'm unsure on how to rewrite the query. should it be written like this:

SET @filterDate =dateadd(day,datediff(day,0,getdate()),0)

Select contact1.contact,
contact1.company,
contact1.lastname,
contact1.department,
contact2.uconfirmno from contact1,contact2
where
contact1.accountno = contact2.accountno
and

WHERE c1.key5 >=@filterDate
AND c1.key5 < DATEADD(day, 1, @filterDate);

and contact1.key4 like 'prin%'

thanks for all the help.. i really do appreciate it.

ron-

ronsoto
Starting Member

5 Posts

Posted - 2009-10-01 : 12:18:35

fyi:

i just tried the query using:

Select contact1.contact,
contact1.company,
contact1.lastname,
contact1.department,
contact2.uconfirmno from contact1,contact2
where
contact1.accountno = contact2.accountno
and
WHERE c1.key5 >=@filterDate
AND c1.key5 < DATEADD(day, 1, @filterDate);
and contact1.key4 like 'prin%'

---

and the query failed with error: BDE Error Cat:Code: [51:3] & Native SQL error: 156.

ron-

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-10-01 : 14:57:42

Ron, where are you running this from? I was assuming that you were just running it in a query window in SQL Server Management Studio, but the error you got does not look like an error you would get from there.

ronsoto
Starting Member

5 Posts

Posted - 2009-10-01 : 16:52:50

i'm running the query from within the GoldMine Application which has a query link into the GM db.

ron-

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-03 : 03:08:51

quote:
Originally posted by ronsoto

i'm running the query from within the GoldMine Application which has a query link into the GM db.

ron-

This forum is for MS SQL Server

Madhivanan

Failing to plan is Planning to fail

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-10-03 : 20:05:47

That is what I was afraid of - now, what you need to do is find out what the rules for variables are in that tool. We were showing you how to setup and write the query using SSMS, and unfortunately it looks like using variables in your tool the way we would in SQL Server does not work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources