Date Difficulties in Qry - weird results

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

SQL Server 2000 Forums

Transact-SQL (2000)

Date Difficulties in Qry - weird results

Author

Topic

CDBanks
Starting Member

23 Posts

Posted - 2005-06-01 : 10:09:59

I have written the following SQL text in an attempt to select all activity related to yesterday in one of our applications for import into our financial system.

SELECT zetup08.id + ' (' + rtrim(convert(char, pybinvoices.postdate,102)) + ')' AS [Batch ID],
rtrim(suppliers.number) AS [Vendor ID],
rtrim(pybinvoices.number) AS [Document Number],
rtrim(convert(char,pybinvoices.postdate,101)) AS [Document Date],
rtrim(pybinvoices.ordernumber) AS [PO Number],
left((convert(char,zetup08.id)),3) + '-' + zetup07_1.code AS [GL Code],
zetup07_1.id AS [GL Description],
gltransactions.valued AS Amount
FROM pybinvoices INNER JOIN
suppliers ON pybinvoices.company = suppliers.intnum INNER JOIN
zetup08 ON pybinvoices.location = zetup08.intnum INNER JOIN
gltransactions ON pybinvoices.intnum = gltransactions.intnum INNER JOIN
zetup07 zetup07_1 ON gltransactions.account = zetup07_1.intnum
WHERE GLTransactions.PostDate = DateDiff(dd,'1/1/1900',GetDate()-1) AND gltransactions.trntype = 1)
ORDER BY [Batch ID], [Document Date], [Vendor ID], [Document Number], [GL Code]

The problem that I am having is that my result set is including transactions that occurred on "10/13/03" and "10/15/03" in addition to yesterdays transactions. For example, today 6/1 my result set included 5/31/05 and some 10/13/03. yesterday my result set included 5/30/05 and the same records for 10/13/03. I think this has to be related to the date in my where clause. I felt that the DateDiff(dd,'1/1/1900',GetDate()-1) should work but do not understand why I am getting the additional records as well. Any suggestions? Thanks in advance for assistance.

Sully
Starting Member

30 Posts

Posted - 2005-06-01 : 10:27:39

never mind...my bad

Stuck in neutral in a pedal to the metal world,
Sully

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 10:53:49

You could do


.... =  CONVERT(datetime,DATEDIFF(dd,'1/1/1900',GETDATE()-1))

OR

.... = DATEADD(dd,DATEDIFF(dd,1,GETDATE()),0)

Andy

Beauty is in the eyes of the beerholder

CDBanks
Starting Member

23 Posts

Posted - 2005-06-01 : 12:25:58

Thanks for the idea. Unfortunately, I still receive the same records as before.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 12:41:26

Does this work


WHERE DATEADD(dd,DATEDIFF(dd,0,GLTransactions.PostDate),0) = DATEADD(dd,DATEDIFF(dd,1,GETDATE()),0)

Just another thought, whats the datatype of GLTransactions.PostDate

Andy

Beauty is in the eyes of the beerholder

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-01 : 12:51:33

We need more info...follow the link below, and I'm sure you'll get a solution very quickly

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CDBanks
Starting Member

23 Posts

Posted - 2005-06-01 : 16:34:28

No, that actually gave me the same results. The field type is DateTime the default it getdate()

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 19:58:42

OK, as Brett said

Follow his link
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 20:02:09

But just 1 last try


WHERE CONVERT(varchar(10),GLTransactions.PostDate,112) = CONVERT(varchar(10),DATEADD(dd,-1,GETDATE()),112)

Beauty is in the eyes of the beerholder

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-01 : 20:14:38

Are you saying that "GLTransactions.PostDate" or "pybinvoices.postdate" has dates equal to "10/13/03"? Because you're selecting "pybinvoices.postdate", which may not exactly correlate to "GLTransactions.PostDate" (you're joining the two tables on "intnum", not the dates). If you can always guarantee that joining the two tables on the "intnum" columns should always produce the same corresponding dates, then you may still have a problem. Otherwise, I would add "GLTransactions.PostDate" to the select list and then you can see if those two dates are not lining up.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 20:43:42

Well spotted! Think it might be a case of couldnt see the WOOD for the TREES
Have you just got out of bed or something?

Our mainframe operates in much the same way, the OrderDate/LineInsertedDate/DecardedDate/InvoiceDate can all be different, so dependant upon the analysis then results can vary!

Andy

Beauty is in the eyes of the beerholder

CDBanks
Starting Member

23 Posts

Posted - 2005-06-02 : 09:21:53

Thank you so much for your help that did it! I have a very small group of people at my company that even understand a portion of this stuff and sometimes you just need a new set of eyes. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources