GETDATE function - SQL Server Forums

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

GETDATE function

Author

Topic

NatalieP
Starting Member

2 Posts

Posted - 2008-12-03 : 07:40:44

Hi there, apologies in advance if this looks like trash, but I am completely new to SQL and I am trying to produce a report which will give me a list based on the Invoice Date (Column BR_InvProfSummary.DateInvoice).
I only want to include anything where the invoice date is less than (or equal to) 14 days ago, I have done the below (based on what I could find on the net) however it doesn't return anything with -14 even though there are invoices within the last 14 days of today :

[=SQL:MTHCOMM=SELECT BR_InvProfSummary.RefCode, RefMaster.Name, BR_InvProfSummary.DateInvoice, BR_InvProfSummary.InvoiceNr, BR_InvProfSummary.TotalBase, BR_InvProfSummary.UserText, BR_InvProfSummary.AcctClientCode FROM BR_InvProfSummary INNER JOIN RefMaster ON BR_InvProfSummary.RefCode = RefMaster.RefCode WHERE BR_InvProfSummary.AcctClientCode IS NOT NULL AND BR_InvProfSummary.DateInvoice BETWEEN GETDATE() AND DATEADD(d,-14,GETDATE()) ORDER BY BR_InvProfSummary.DateInvoice,BR_InvProfSummary.InvoiceNr]

Any help would be much appreciated and I thank anyone in advance for their efforts and time

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-03 : 07:47:34

between needs the lower value first.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 08:54:54

Should be

BR_InvProfSummary.DateInvoice >=DATEADD(day,DATEDIFF(day,0,GETDATE()),-14) AND
BR_InvProfSummary.DateInvoice <DATEADD(day,DATEDIFF(day,0,GETDATE()),1)

Madhivanan

Failing to plan is Planning to fail

NatalieP
Starting Member

2 Posts

Posted - 2008-12-03 : 09:00:57

Many thanks to webfred I used that and it worked.
Thanks also to madhivanan for his advice that I received after webfred's, I will also give this a try if necessary.
A super speedy and helpful response indeed!

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 12:33:23

also, for days, you don't really need the DATEADD function. GETDATE()-14 equals 14 days ago... if you've got a lot of results, that may speed it up a bit.

___________________________
Geek At Large

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-05 : 01:26:32

quote:
Originally posted by jholovacs

also, for days, you don't really need the DATEADD function. GETDATE()-14 equals 14 days ago... if you've got a lot of results, that may speed it up a bit.

___________________________
Geek At Large

See the difference


SELECT 
	DATEADD(day,DATEDIFF(day,0,GETDATE()),-14),
	GETDATE()-14

Madhivanan

Failing to plan is Planning to fail

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-05 : 08:54:03

Maybe it's a matter of interpretation.

quote:

I only want to include anything where the invoice date is less than (or equal to) 14 days ago

I tend to consider a day as 24 hours, but that may not be normal.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources