11 replies to this topic

[SterAllures]

Hiya CodeCall Members!

I yet again have another problem, The first time I started a thread here I was a real newB. I really hope I've

improved that because I did alot of research!

I now got access to a database, the sample database of Northwind that you can download from the microsoft site, just

to train.

The problem is similar to my other problem I had before with the between but I think I need somenthing else to

measure the outstanding invoices.

Problem: Well I want to check the outstanding invoices between 30 and 35 days.

Now I want to measure this with the current date at the time I request it so it's up-to-date everytime.

Well I got the following

SELECT i.CompanyName, i.InvoiceID, i.InvoiceDate

FROM Invoice As i

Now I would like to see the outstanding days since the Invoice was made (Only in days not in time)

So I want somenthing Like This (It's wrong I know But just to give an idea hope you understand it)
WHEN Paydate >= Invoice Date
But Only When when the Difference of the PayDate and InvoiceDate are BETWEEN 30 AND 35 Days of the current date.

I really hope you guys can understand it! Hope to hear from you!

Thanks In Advance!
//SterAllures

[Orjan]

with MySQL:

select * from Invoice where datediff(InvoiceDate,curdate()) between 30 and 35

[SterAllures]

But Would that also work in a Microsoft SQL Server Database?

[Orjan]

I'm not sure they have the datediff function. but try it. it can't do any errors :-)

[SterAllures]

Oke well I looked if it worked but it unfortunately didn't got this error back

Quote

'curdate' is not a recognized built-in function name.

It was worth a try :D

EDIT:
Ow I got somenthing else! I tried it with the getdate() fucntion and I got another error that's mayB easy to fix got this error back now!

Quote

The datediff function requires 3 argument(s).

Thanks for this start we are a little further !

[SterAllures]

Oke Looked around and found this. these are the 3 arguments they need

Quote

DATEDIFF ( datepart , startdate , enddate )

Where datepart is a year, day, minute or etc.

with startdate it says this:

Quote

startdate
Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.

To avoid ambiguity, use four-digit years. For information about two digits years, see two digit year cutoff Option.

and here an example:

Quote

SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

[SterAllures]

I THINK I FOUND THE ANSWER! (Not sure hope you can check it for me orjan!)
Here is it!

select *

from Invoice

where datediff(day, InvoiceDate, getdate()) between 30 and 35

Thank you so much for your help! because of your idea I think we got an answer!:D
Cool man

[Orjan]

Great, then I think you've solve this?

[SterAllures]

orjan said:

Great, then I think you've solve this?

Well It's because of you with a great idea!

Butl I'm still not sure if it's the correct code to give back the request I want. But I tried it in the sample database with the HireDate of an employee and it didn't gave any rows back because you can't have a hired person in the future.
But it also didn't gave any errors back.

But I'll try it later this week than with the correct database.

But when you look at it does it look right you think?

[Orjan]

sure do.

[SterAllures]

orjan said:

sure do.

Well thanks for your help on this matter!
you gave me the golden tip :D.

I'll let you know if it works on the way I want it.

[SterAllures]

Hiya Orjan,

I've got the best news!

I've tried it in the sample database and it worked!!! from the 400 records it gave 30 back!
and when I changed the BETWEEN values to other days it gave different values back!

So I guess that means it works!
Hope I can try it soon at the real database but this is great to know.

Thanks for your help!!!:thumbup: