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 could
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
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. Values
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#
OR
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
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
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
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
real date
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
)
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.
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...
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
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
found
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 example I
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
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:
cfset findMONTHS =
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
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.
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: [EMAIL
-
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 create
your start and end dates.
CFSET
ERE DateEntered = '#urlDecode(url.dateRange1)#' AND DateEntered =
'#urlDecode(url.dateRange2)#'
ORDER BY DateEntered
/cfquery
Hope this helps!
Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Origi
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 ?
-
Using my
= "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 crea
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 ?
-
Using my code:
CFSET startdate = "#
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
helped
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
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
I think you want to use the CreateODBCDate() function to create your start and end
dates.
CFSET startdate = "#CreateODBCdate(Year,Month,Day)#"
CFSET enddate = "#CreateODBCdate(Year2,Month2,Day2)#"
Then refer to those in your query *without* 's. (the CreateODBCDate() function puts
them in for
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 it the single paranth
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 Message
30 matches
Mail list logo
