Solved

Handling datevalues in SQL

Posted on 2009-05-07
3
258 Views
Last Modified: 2012-05-06
Hi I'm having some trouble getting a recordset to only include records dated > 1yr ago.

Fromdate = DateValue(DateAdd("d", -365, Now))
Todate = DateValue(Now)
       
        Sql = ""
        Sql = Sql & " Select [R_PatID] ,  [R_TestDate] from TEST_RESULTS"
        Sql = Sql & " where [R_PatID] = " & DSCohort.Fields("Pat_ID")
        Sql = Sql & " and  [R_TestDate] between " & DateValue(Fromdate) & " and " & DateValue(Todate)
        Sql = Sql & " order by [R_TestDate] asc"



The data is stored in a date time field (dd/mm/yyyy) in an excel 2003 file but I'm getting old dates such as 14/01/2003, 24/10/2004  being returned on Debug.Print DSResultRecords.Fields("R_Testdate") & " " & DSResultRecords.RecordCount

I have tried using :
1/ datevalue [r_testdate]  -  syntax error
2/ [R_testdate] > ' " & Fromdate & " ' "  - returns old values too.
3/ year [R_testdate] > " & year(fromdate - 1) . Works but includes all of last years dates ie 17months worth if I run it in may 09
 

Is there another way of doing this query to return only records dated from exactly 12 months ago??

Many thanks

0
Comment
Question by:peterdarazs
  • 2
3 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24333390
why dont you use DateDiff instead?

DateDiff("interval","olddate","newdate")

set interval to be "d" for number of days difference
or "yyyy" or years difference

eg

Sql = Sql & " and  DateDiff('yyyy',[R_TestDate],now())  >= 1"



0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24333412
to use your existing method, you could format the dates
eg

Fromdate = format(DateAdd("yyyy", -1, Now()),"yyyymmdd")
Todate = format(now(),"yyyymmdd")

 Sql = Sql & " and  Format([R_TestDate],'yyyymmdd') between " & Fromdate & " and " & ToDate

0
 

Author Closing Comment

by:peterdarazs
ID: 31579283
Ok, thanks  - that explains it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now