Date Diff

[Shin25]

Hi All

Iam new to sql coding and working with SQL 2005, I need to know how to structure my query or what I am doing wrong with my query.

I have 2 datetime fields, field1 and field2.

I need my query to return results where the number of days are within a 14 day period from field1 datetime.

My sql code :-

SELECT
NetUtil.dbo.Reports._DateCreated,
NetUtil.dbo.Reports.OriginalPolicyInceptDate,
NetUtil.dbo.Reports."_ProcessName",
( datediff(dd,NetUtil.dbo.Reports."_DateCreated",'04/01/2008 00:0:0') )
FROM
NetUtil.dbo.Reports
WHERE
(
( datediff(dd,NetUtil.dbo.Reports."_DateCreated",'04/01/2008 00:0:0') between 1 and 14)

Please can you help......

 

[r937]

i don't see field1 or field2 in your query at all

could you try explaining the problem again please?

also, you did not indeicate whether your query is working correctly, and if not, what is it actually doing that is wrong

r937.com | rudy.ca

 

[bloomlight]

Below is the "datediff" that I used in my query:

datediff(dd, date1, date2) "days

 

[bborissov]

bloomlight I don't understand.
What is your suggestion?
DateDiff is well described in BOL


Shin25,
I am not sure what you want. You have TWO fields in your table but you use only ONE and a constant in the query. Could you be a little more specific?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Shin25]

I have tried that and I am not getting back the correct results.....any one any other ideas?

 

[ca8msm]

any one any other ideas?

Yes, you could try answering bborissov's questions.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[r937]

you have tried what? could you show us again, please? and show us what you are getting from it?



r937.com | rudy.ca

 

[bborissov]

Shin25,
From my observation you (me, everybody) got what He/She ASK, not what He/She want to ask. So the question here is what you want? And how you ASKING for it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[PDreyer]

I have 2 datetime fields, field1 and field2.

I need my query to return results where the number of days are within a 14 day period from field1 datetime.

where field1 >= dateadd(dd, datediff(dd,'20000101',field2)-14, '20000101')
  and field1 <  dateadd(dd, datediff(dd,'20000101',field2)+1 , '20000101')

You might have to adjust the -14 and +1 depending on what you mean by "within a 14 day period

 

[bloomlight]

You may want to read this one: faq183-6628. Hope it helps.

 

[Shin25]

Hi All

sorry if I was being vague in my description...

Let me explain...

SELECT
NetUtil.dbo.Reports._DateCreated,
NetUtil.dbo.Reports.OriginalPolicyInceptDate,
NetUtil.dbo.Reports."_ProcessName",
( datediff(dd,NetUtil.dbo.Reports."_DateCreated",'04/01/2008 00:0:0') )
FROM
NetUtil.dbo.Reports
WHERE
(
( datediff(dd,NetUtil.dbo.Reports."_DateCreated",'04/01/2008 00:0:0') between 1 and 14)

The 2nd fields I am referring to is the datetime already entered in the datediff statement ('04/01/2008 00:0:0') as this datetime will be inputted from a front end system.

So this field NetUtil.dbo.Reports."_DateCreated, will remain static and the datediff is calculating with is a user prompt field. According to the data I have available I should only get 2 records back. Which is the case if I remove '04/01/2008 00:0:0' and put the field NetUtil.dbo.Reports.OriginalPolicyInceptDate. But when I change this field to a manual input of a datetime I am getting incorrect results. Any ideas?

I hope I make sense...

Many Thanks...

 

[johnherman]

You should forget about datediff, since it appears that a datediff solution will require a subquery. PDreyer has provided a very good solution using dateadd.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright