1 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: Between two dates

Using Query design in Access 2003 (my usual first step before trying to incorporate the resultant SQL into an XML file), I'm trying to select records from the Sample table where the VAGUE_DATE_START lies between two specified dates.

I've discovered all sorts of other conversions such as:
CONVERT (char(10), DATEADD(DAY, dbo.SAMPLE.VAGUE_DATE_START, '00:00:00'), 103) to express Recorder's VAGUE_DATE_START number in text,

YEAR(dbo.SAMPLE.VAGUE_DATE_START) to get the Year,

DATEPART(q, dbo.SAMPLE.VAGUE_DATE_START) to get the Quarter

DATEADD(m, 0, '01/04/2005') and DATEADD(m, 12, '01/04/2005') to get an expression for the start and end of the financial year

However I cannot find a command like the old Access's "Between #01/04/2005# and #31/03/2006#".
I don't want to cheat by trying to work out what numerical value is the same as these two dates as this would just leave a problem for the future, I'm after some simple expression.
Has anyone cracked it?

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Between two dates

I've got a UDF from Mike Weideli that does the job. You can get download it here:

http://forums.nbn.org.uk/uploads.php?file=LC_ToRataDie.zip

It takes a date in the format DD/MM/YYYY as an argument and returns a number as used by Recorder for dates. You would use it like this, for example.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website