>
> > If this is MS SQL server you have the BETWEEN function.
>
-1
BETWEEN... *barf*
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusio
If this is MS SQL server you have the BETWEEN function.
WHERE #now()# BETWEEN EventStartDate AND EventFinishDate
Wil Genovese
Sr. Web Application Developer/
Systems Administrator
Wil Genovese Consulting
wilg...@trunkful.com
www.trunkful.com
On Jun 13, 2010, at 9:07 PM, Jim Eisenhauer wrote:
WHERE EventStartDate>= #now()# AND EventFinishDate<= #now()#
You had your end date looking for a date ">=" Now()
Jim Eisenhauer
On Sun, Jun 13, 2010 at 6:41 PM, Roger Austin wrote:
>
> On 6/13/2010 9:30 PM, Kevin Parker wrote:
> >
> > Greatly appreciate another set of eyes looking at this pl
Penny dropped - thank you! I was over complicating it.
++
Kevin Parker
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
-Original Message-
From: Roger Austin [mailto:raust...@nc.rr.com]
Sent: Monday, 14 June 2010 11:42
To: cf-talk
Subject: Re: Date range
On 6/13/2010 9:30 PM, Kevin Parker wrote:
>
> Greatly appreciate another set of eyes looking at this please.
>
> Picking events from a table based on dates so that only current or future
> events show up. Date values can not be NULL.
>
> (EventStartDate>= #now()#) AND (EventFinishDate>= #now()#)
>
On Tuesday 02 Dec 2008, Jim McAtee wrote:
> I have a table where I want to store begin and end dates _without_ a year.
> For example, 'Dec 4' through 'Jun 15'. How can I best represent this in
> the database?
Use day of year i.e 1-366
--
Tom Chiverton
Helping to continuously maximize models
If it was me, I would pick an arbitrary, but consistent year to use with
the date, but just don't display the year when you format the dates for
your view.
In your select example #today# would need to have the month and day
pulled out and concatenated with your dummy year.
That might sound like e
> '1202' >= begindate OR '1202' <= enddate );
>
>
>
> - Original Message -
> From: "Barney Boisvert" <[EMAIL PROTECTED]>
> To: "cf-talk"
> Sent: Tuesday, December 02, 2008 12:31 PM
> Subject: Re: D
202' >= begindate OR '1202' <= enddate );
- Original Message -
From: "Barney Boisvert" <[EMAIL PROTECTED]>
To: "cf-talk"
Sent: Tuesday, December 02, 2008 12:31 PM
Subject: Re: Date Range Without Year
> Select all events between February 12
truct SELECT queries using >=, <=, or BETWEEN. Using expressions with
> MySQL functions in the SELECT would be ok, if it can be done.
>
>
> - Original Message -
> From: "Barney Boisvert" <[EMAIL PROTECTED]>
> To: "cf-talk"
> Sent: Tues
PROTECTED]>
To: "cf-talk"
Sent: Tuesday, December 02, 2008 11:52 AM
Subject: Re: Date Range Without Year
> You could just use numbers to represent the dates. Year runs from 101
> to 1231 (thats MDD), and easily create from a date like this:
> #month(now()) * 100 + day(now
You could just use numbers to represent the dates. Year runs from 101
to 1231 (thats MDD), and easily create from a date like this:
#month(now()) * 100 + day(now())#. Strings would also work instead of
numbers: "0101" to "1231", and created via #dateFormat(now(),
"mmdd")#.
cheers,
barneyb
On Tu
>>So, if they only enter 2007 and nothing else, 01/01/2007 is getting
recorded
Well, this will be fine when looking for events <= 06/30/2007 for instance,
since 0/0/2007 stands for "any date in 2007".
but what if looking for events >= 06/302007?
01/01/2007 won't come out and it should, since it c
On Mon, Mar 24, 2008 at 7:29 PM, Les Mizzell <[EMAIL PROTECTED]> wrote:
> denstar wrote:
> > Are they against letting you add a real date field?
>
> Seeing there's already several hundred records in the database, some
> with dates, some with NONE - which is exactly what they want - adding a
> r
On Mon, Mar 24, 2008 at 7:26 PM, Les Mizzell <[EMAIL PROTECTED]> wrote:
...
> What's the easiest way to search for values in a list starting at
> position X and ending at position Y?
Off the top of my head, I'd do a listSort, and then iterate.
Dunno if the list position idea would work for the
Just to recap - client had a database that was storing the dates of
transactions - but - they wanted to be able to only enter a year, or
just a year and month without an actual date. So, three columns were set
up day|month|year and the values were being stored as integers ...
I ended up adding
Lez, this has been asked, but not answered. What do the zeros mean? Do
they indicate a date range? Does 2007.0.0 mean the entire year of 2007?
Does 2007.10.0 mean the entire month of October in 2007? Does 0.0.0 mean
always?
If so, I'd create two date columns, begin_dt and end_dt. I'd then wri
Ok, how about this, a bit ugly, but should work on any database:
WHERE
(YEAR > #form.from_y#
OR (YEAR = #form.from_y#
AND (MONTH = 0
OR (MONTH >= #form.from_m#
AND (DAY = 0 OR DAY >= #form.from_d#)
AND
(YEAR < #form.to_y#
Les Mizzell wrote:
> I've got a database in which dates are stored as integers in three
> separate columns as: day|month|year
>
> This was done because the client wished to create records that could
> contain only a year, or just a year and month, or year, month, and day,
> or no date at all. V
This is ugly and uses T-SQL syntax but you get the idea.
SELECT id
FROM (
SELECT id, CAST(
CAST(year AS varchar)
+ '-' + CAST(CASE month WHEN 0 THEN 1 ELSE month END AS varchar)
+ '-' + CAST(CASE day WHEN 0 THEN 1 ELSE day END AS varchar)
AS datetime) as realdate
FROM aaa
) tmpT
WHERE realdate BET
>>Try searching for dates between 10/15/2006 and 04/14/2007
>>See the problem?
Yeah, my answer was a bit too easy ;-)
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send any spam to this address: [EMAI
>The problem with that is the same problem as the previous example I
>posted though.
>
>Try searching for dates between 10/15/2006 and 04/14/2007
Given those search dates, how do you want to handle records with a year only
(2006 or 2007). Same question for the month.
~~
denstar wrote:
> Are they against letting you add a real date field?
Seeing there's already several hundred records in the database, some
with dates, some with NONE - which is exactly what they want - adding a
real date field would be tough. It was a hard argument to start with,
and I let them
Ahhh- here's an idea!
OK, in theory...
Let's just look at just the month for starters...
form.startMONTH
form.endMONTH
Each has a value of 1 - 12
but, let's use that as LIST START AND END POSITIONS
Set a list containing 2 years worth of months:
form.startMONTH would equal the starting positi
Are they against letting you add a real date field?
I've been there, in spades, and there's always a way through. Sometimes
it's a lot easier, and not as much fun, as banging your head against the
same angle of attack for hours tho. :-)
Perhaps add a date field, and then make some magic that tu
Claude Schneegans wrote:
> How about:
> and YEAR >= #form.from_y# and YEAR <= #form.to_y#
> and ((MONTH >= #form.from_m# and MONTH <= #form.to_m#) OR MONTH=0)
> and ((DAY >= #form.from_d# and DAY <= #form.to_d#) OR DAY=0)
The problem with that is the same problem as the previous exa
> I'd been trying to come up with a way to do this, but the columns with a
> "0" in them are throwing me off, since "0" isn't a valid date value.
You could use CASE or IF to fill in dummy values for those zeroes, but then,
if you substituted 1 (January) for the month for example, it wouldn't be
How about:
and YEAR >= #form.from_y# and YEAR <= #form.to_y#
and ((MONTH >= #form.from_m# and MONTH <= #form.to_m#) OR MONTH=0)
and ((DAY >= #form.from_d# and DAY <= #form.to_d#) OR DAY=0)
--
___
REUSE CODE! Use custom tags;
See http://www.conten
> I'd been trying to come up with a way to do this, but the columns with a
> "0" in them are throwing me off, since "0" isn't a valid date value.
and I might add - it was put a "0" in there, or allow a null, which
would have been worse...
> The idea being to parse the separate date columns into usable dates in the
> subquery, and then join that to the superquery as needed.
I'd been trying to come up with a way to do this, but the columns with a
"0" in them are throwing me off, since "0" isn't a valid date value.
You might be able to join the table to itself in a query, and on that join
concat your date integers into actual date values.
Something like...
SELECT whatever
FROM thetable t
INNER JOIN ( SELECT id, CONCAT(yearcol,'/',monthcol,'/',daycol) AS gooddate
FROM thetable
)
to:[EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 10:08 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> BEGIN
>
> declare @yourTable table(
> EntryNo int,
> startDateField datetime,
> endDateField datetime
> )
>
>
Dayquil and admit defeat.
Steve
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 10:08 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> BEGIN
>
> declare @yourTabl
DURETTE, STEVEN J (AIT) wrote:
>
> BEGIN
>
> declare @yourTable table(
> EntryNo int,
> startDateField datetime,
> endDateField datetime
> )
>
> insert into @yourTable(entryNo, startDateField, endDateField) values(1, '11-25-2003', '12-15-2003')
> insert into @yourTable(entryNo, startDateFi
d up!) I tried solving
both problems at the same time instead of trying to find the best solution
for each.
So, I stand humbled at your streamlining expertise.
Steve
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 4:22 PM
To: CF-Talk
Subj
Jochem van Dieten wrote:
> DURETTE, STEVEN J (AIT) wrote:
>
>>The problem with your change is that it won't pick up partial overlays.
>
> Just run it. We actually need to test it twice, proving that the
> negator is the same as the operator is too much work for now:
On second thought, the proof
Tony Weeg wrote:
> sure...where?
http://register.microsoft.com/mswish/suggestion.asp?from=cu&fu=%2Fisapi%2Fgomscom%2Easp%3Ftarget%3D%2Fmswish%2Fthanks%2Ehtm
> www.microsoft.com/likeTheyCare.cfm
You mean you buy software from a company that doesn't care about
its customers?
Jochem
--
Who nee
Subject: Re: Date Range Query Issues
Tony Weeg wrote:
> not going to work in sql server 2000, not from what I can see in the
> docs
File an enhancement request :-)
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
[Todays Threads]
[This Message]
[Subscr
Tony Weeg wrote:
> not going to work in sql server 2000, not from what I can see in the
> docs
File an enhancement request :-)
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
DURETTE, STEVEN J (AIT) wrote:
>
> The problem with your change is that it won't pick up partial overlays.
Just run it. We actually need to test it twice, proving that the
negator is the same as the operator is too much work for now:
BEGIN;
CREATE TABLE yourTable (
startDateField DATE,
endD
Jochem,
OVERLAPS isn't a valid MSSql2K TSQL command. I looked it up in the MSSQL
Books online.
Steve
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 3:38 PM
To: CF-Talk
Subject: Re: Date Range Query Issues
Mickael
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 3:31 PM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> But you can use between
>
> select count(idField)
> from yourtable
>
Mickael wrote:
>
> Could you show me a statement where that is used, it sounds interesting just don't know how it is used.
Instead of
SELECT x
FROM yourtable
WHERE startDateField < '#formEndDate#'
AND endDateField > #formStartDate#'
one would use:
SELECT x
FROM type = 'checktype'
DURETTE, STEVEN J (AIT) wrote:
>
> But you can use between
>
> select count(idField)
> from yourtable
> where type = 'checktype'
> and (startDateField between 'startdate' and 'enddate'
> OR endDateField between 'startdate and 'enddate')
That won't work, it won't detect the following overla
Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 12:05 PM
To: CF-Talk
Subject: RE: Date Range Query Issues
not going to work in sql server 2000, not from what I can see in the
docs
..tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL P
Hi Jochem,
Could you show me a statement where that is used, it sounds interesting just don't know how it is used.
Thanks
Mike
- Original Message -
From: Jochem van Dieten
To: CF-Talk
Sent: Tuesday, December 02, 2003 10:12 AM
Subject: Re: Date Range Query Issues
Ca
27;)
Of course change all of the 'variables' to either cfqueryparams in cf or
@variables in TSQL.
Steve
-Original Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 12:05 PM
To: CF-Talk
Subject: RE: Date Range Query Issues
not going to
, 2003 10:22 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
Oh wow... I didnt know about that... I'm using MSSQL 2000...
I'll try it out when I get back from my meeting. :)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, O
Oh wow... I didnt know about that... I'm using MSSQL 2000...
I'll try it out when I get back from my meeting. :)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL
ottrell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 10:03 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
Hey Jochem :)
1 - Do you mean natively or the way I have the db set up? If it's the
latter, I don't have any constraints for overlaps in the db design. Each
&qu
Candace Cottrell said:
>
> 1 - Do you mean natively or the way I have the db set up?
Natively, OVERLAPS is a SQL predicate:
('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15')
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Hey Jochem :)
1 - Do you mean natively or the way I have the db set up? If it's the latter, I don't have any constraints for overlaps in the db design. Each "shift" or date range is its own record. The id is an identity field.
Candace K. Cottrell, Web Developer
The Children's Medical Center
One
Candace Cottrell said:
>
> Start_Month 12
> Start_Day 1
> Start_Year 2003
> Start_Time 8
>
> End_Month 12
> End_Day 15
> End_Year 2003
> End_Time 8
> I know I need to pull a query that brings back the records that
> would be overlapped. And if that recordset is empty, proceed with
> the insert. Ot
1.] Try this...
#dateFormat(tempDate,'
')#
2 & 3 .] Notice that start date is set with cfparam. Using this way, you
can populate the variable beforehand
anyway you want (database, user, session vars,
yep you can use CVDate() or Cdate depending on the access version to turn a
string into a date. e.g
where CVDate( Month & '01/' & '/' & Year) between #Oct/01/2001# and
#Feb/01/2002#
-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: 19 February 2002 21:34
To: CF-Talk
i guess i'm one of the "others"
heh
- Original Message -
From: Robert Orlini <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Sent: Wednesday, April 04, 2001 12:07 PM
Subject: date range search - thanks!
> Thanks to all (Lamon, Steve, Jason, Jay, Phillip, Paul, and others) who
> h
Access and it returned records
within the specified ranges.
Alec
> -Original Message-
> From: Robert Orlini [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 04, 2001 11:55 AM
> To: CF-Talk
> Subject: RE: date range search
>
>
> Thanks for the suggesti
Thanks.
>
> Robert O.
>
> >-Original Message-
> >From: Lamon, Alec [mailto:[EMAIL PROTECTED]]
> >Sent: Tuesday, April 03, 2001 3:04 PM
> >To: CF-Talk
> >Subject: RE: date range search
> >
> >
> >I think you want to use the CreateODB
> Thanks for the suggestion. It didn't work though. I have a form with a
> dropdown menu. Can dates in an Access table between: 01/01/00 and 01/01/01
> be searched ?
> -
> Using my code:
>
>
>
>
>
>
> SELECT * FROM wwebsales
> where orderdate between #start
om: Robert Orlini [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 04, 2001 4:55 PM
To: CF-Talk
Subject: RE: date range search
Thanks for the suggestion. It didn't work though. I have a form with a
dropdown menu. Can dates in an Access table between: 01/01/00 and 01/01/01
be searched ?
---
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Original Message -
From: "Robert Orlini" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, April 04, 2001 9:54 AM
Subject: RE: date range sea
ce = "wwebsales"
--
Thanks.
Robert O.
>-Original Message-
>From: Lamon, Alec [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, April 03, 2001 3:04 PM
>To: CF-Talk
>Subject: RE: date range search
>
>
>I think you want to use the CreateODBCDate() function to cr
doh...that's what I meant...quote not parenthesis
-Original Message-
From: Kevin Schmidt [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 03, 2001 5:15 PM
To: CF-Talk
Subject: Re: date range search
Lose the single quotes around your date variables in your SQL.
- Original Me
Lose the single quotes around your date variables in your SQL.
- Original Message -
From: "Tumy, Brad" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, April 03, 2001 2:43 PM
Subject: RE: date range search
> shot in the dark...is i
I think you want to use the CreateODBCDate() function to create your start and end
dates.
Then refer to those in your query *without* 's. (the CreateODBCDate() function puts
them in for you):
SELECT * FROM wwebsales
where orderdate between #startdate# and #enddate#
> -Original Messa
take out the single quotes from the query.
-Original Message-
From: Robert Orlini [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 03, 2001 3:30 PM
To: CF-Talk
Subject: date range search
I'm working on a search form which can search between a range of dates. I'm
using the code below and
shot in the dark...is it the single paranthesis around the date variable?
Brad
-Original Message-
From: Robert Orlini [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 03, 2001 4:30 PM
To: CF-Talk
Subject: date range search
I'm working on a search form which can search between a range of
67 matches
Mail list logo
