I tried that one as well. I kept getting:
Syntax error converting the varchar value '/' to a column of data type int.
So then I went to:
update oncall_shifts set shift_start = cast((start_month + start_day + start_year) as datetime)
and got:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Thoughts? *meanwhile, I'll keep trying*
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 PROTECTED] 1/29/2004 1:33:06 PM >>>
Candace,
This should be :
update oncall_shifts set shift_start = cast((start_month + '/' + start_day +
'/' + start_year) as datetime)
Steve
-----Original Message-----
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 29, 2004 1:28 PM
To: CF-Talk
Subject: Re: Dates - ranges overlapping months... rut-roh
Hey Jochem, I am using Transact-SQL and converted everything but:
UPDATE TABLE table SET shift_start = to_date( start_year || '-'
|| start_month || '-' || start_day, "yyyy-mm-dd");
I tried to do:
UPDATE ONCALL_SHIFTS
SET shift_start = CAST(start_month&start_day&start_year AS DATETIME)
But it is setting the shift_start to 1/1/1900??
Thanks for all your help!!
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org <http://www.childrensdayton.org>
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 1/29/2004 11:22:01 AM >>>
Candace Cottrell wrote:
>
> <cfqueryparam cfsqltype="cf_sql_integer" value="#page[j][i]#"> BETWEEN
START_DAY AND END_DAY
> AND
> <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.M#"> BETWEEN
START_MONTH AND END_MONTH
> AND
> <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.Y#"> BETWEEN
START_YEAR AND END_YEAR
>
> does not work when you have a date range that spans more than one month.
> Start_Month = 1
> Start_Day = 18
> Start Year = 2004
>
> End_Month = 2
> End_Day = 8
> End_Year = 2004
>
> Basically, while looping, if my #page[j][i]# was on the number 3, it would
return false, because 3 is not between 18 and 8.
How about creating an extra field that stores a date and query
that? You do not have to change any UI for that, you just
maintain it through a trigger.
Pseudocode (PL/SQL has been a while):
ALTER TABLE table ADD COLUMN shift_start DATE;
UPDATE TABLE table SET shift_start = to_date( start_year || '-'
|| start_month || '-' || start_day, "yyyy-mm-dd");
CREATE TRIGGER ON table AFTER UPDATE, INSERT
BEGIN
SET NEW:shift_start = to_date( start_year || '-' ||
start_month || '-' || start_day, "yyyy-mm-dd");
END;
After this, your shift_start will always contain the start date.
Do the same for the end_date and you can do a simple between on
the entire date.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
