We help IT Professionals succeed at work.

Criteria to pull a date range

IEHP1
IEHP1 asked
on
I am simply trying to pull the date range as the syntax below says, but it won't pull anything for last year. I mean, the oldest records pulled are Jan 6, 2011 and I see in the table it is using that there are records in December and November 2010.

Between DateSerial(Year(Date()),Month(Date())-12,1) And DateSerial(Year(Date()),Month(Date()),0)
Comment
Watch Question

If you want exactly a year from current date , use
DateSerial(Year(Date()) - 1,Month(Date()), Day(Date())) And DateSerial(Year(Date()),Month(Date()), Day(Date()))

Open in new window

CERTIFIED EXPERT

Commented:
It might help if you mentioned which db you're using.

 James
Dave FordSoftware Developer / Database Administrator
CERTIFIED EXPERT

Commented:

Wow, this kind of thing is way easier in DB2:

select someColumn
  from MyTable
 where someDate between current date - 1 year and current date

Open in new window

Author

Commented:
I appreciate all of the comments, but I believe that my question has been a little misinterpreted.

I am trying to pull a Rolling 12 Month date range meaning, for my report, I need it to show everything from the beginning of November 2010 until the end of October 2011.

I used your criteria, elimesika. Unfortunately, it returns the same results (earliest records have 06 January 2011). I know that the records are there for 2010 in the table being queried and when I used DateAdd("m",-13,Left(Date(),2) & "/" & Right(Date(),4))  it pulled every record having 10/01/2010 in the date/time field.

Please help me to figure this out.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Isn't this just a simple as this, or am I missing something?

Between DateAdd("d",-365,Date()) And Date()

~bp

Author

Commented:
Sorry, that syntax gives me a Data Type Mismatch? I can provide a sample database if you would like?

Author

Commented:
can anyone help me with this please?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
What's the data type of the field you are adding this condition to?

~bp

Author

Commented:
it is a True Date/Time field.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
What SQL engine are you running on? (SQL Server, Oracle, MySQL, ...)

~bp

Author

Commented:
SQL Server
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Then this should work:

Between DateAdd("d",-365,GetDate()) And GetDate()

~bp
Which version of SQL Server?

Also, when you say that you can "see" the data with earlier dates, exactly how are you "seeing" them?

Finally, what is the SQL statement you are using when you say "when I used DateAdd("m",-13,Left(Date(),2) & "/" & Right(Date(),4))  it pulled every record having 10/01/2010 in the date/time field"

Author

Commented:
Billprew:  the GetDate() function you told me to put came back with an error message stating: "Undefined function 'GetDate' in expression".  

8080 Driver:  I used my old criteria (since I have been trying to make it pull the right data so much) and it doesn't pull any records now? So I am not sure what to do?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Hmmm, that's odd because I'm pretty sure that's been in SQL Server for a long time.  It certainly worked for me here...

Can you supply the surrounding context for this between statement, where is it benig used?

~bp
CERTIFIED EXPERT

Commented:
You said that you're using "SQL Server".  You did mean MS SQL Server, correct?  If so, which version?  It has had GetDate since MS SQL Server 2000, and I doubt they've removed that function from the latest version.  Also, you used DateSerial in your first post, which MS SQL Server didn't used to have.  Maybe they've added it, or maybe you have your own implementation.  If it's a function that was added to your system, then there's always the possibility that it's not working properly.

 James
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Agreed, we need

(1) exact details on what SQL platform and version you are running

(2) more context on where you are using the BETWEEN clause, what's the surrounding code, what type of object is it in, etc

~bp
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Based on the asker's other questions, it's the revolutionary, fool-proof, in-no-way-hackable "MS Access" DB engine ;)
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
Ah, if MS Access then I think it's as simple as:

Between Date()-365 And Date()

~bp

Author

Commented:
Actually it accounted for the month as well using a Between....And Date Range. I figure it out.

Author

Commented:
I chose you because you wanted to help me.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Thanks.

~bp

Explore More ContentExplore courses, solutions, and other research materials related to this topic.