Re: Dates - ranges overlapping months... rut-roh
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, -mm-dd); CREATE TRIGGER ON table AFTER UPDATE, INSERT BEGIN SET NEW:shift_start = to_date( start_year || '-' || start_month || '-' || start_day, -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]
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, -mm-dd); I tried to do: UPDATE ONCALL_SHIFTS SET shift_start = CAST(start_monthstart_daystart_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 [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, -mm-dd); CREATE TRIGGER ON table AFTER UPDATE, INSERT BEGIN SET NEW:shift_start = to_date( start_year || '-' || start_month || '-' || start_day, -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]
RE: Dates - ranges overlapping months... rut-roh
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, -mm-dd); I tried to do: UPDATE ONCALL_SHIFTS SET shift_start = CAST(start_monthstart_daystart_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, -mm-dd); CREATE TRIGGER ON table AFTER UPDATE, INSERT BEGIN SET NEW:shift_start = to_date( start_year || '-' || start_month || '-' || start_day, -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]
RE: Dates - ranges overlapping months... rut-roh
Hey Steve! 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, -mm-dd); I tried to do: UPDATE ONCALL_SHIFTS SET shift_start = CAST(start_monthstart_daystart_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, -mm-dd); CREATE TRIGGER ON table AFTER UPDATE, INSERT BEGIN SET NEW:shift_start = to_date( start_year || '-' || start_month || '-' || start_day, -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]
RE: Dates - ranges overlapping months... rut-roh
Doh. Try this: update oncall_shifts set shift_start = cast((cast(start_month as varchar) + '/' + cast(start_day as varchar) + '/' + cast(start_year as varchar)) as datetime) sorry about that. -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 29, 2004 1:53 PM To: CF-Talk Subject: RE: Dates - ranges overlapping months... rut-roh Hey Steve! 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 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, -mm-dd); I tried to do: UPDATE ONCALL_SHIFTS SET shift_start = CAST(start_monthstart_daystart_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 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, -mm-dd); CREATE TRIGGER ON table AFTER UPDATE, INSERT BEGIN SET NEW:shift_start = to_date( start_year || '-' || start_month || '-' || start_day, -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]
RE: Dates - ranges overlapping months... rut-roh
Thanks Steve :) That was one of the problems. The other was that there was a trigger on the column. I had to remove it before running the update. 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 2:07:35 PM Doh. Try this: update oncall_shifts set shift_start = cast((cast(start_month as varchar) + '/' + cast(start_day as varchar) + '/' + cast(start_year as varchar)) as datetime) sorry about that. -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 29, 2004 1:53 PM To: CF-Talk Subject: RE: Dates - ranges overlapping months... rut-roh Hey Steve! 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 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, -mm-dd); I tried to do: UPDATE ONCALL_SHIFTS SET shift_start = CAST(start_monthstart_daystart_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 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, -mm-dd); CREATE TRIGGER ON table AFTER UPDATE, INSERT BEGIN SET NEW:shift_start = to_date( start_year || '-' || start_month || '-' || start_day, -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]
RE: Dates - ranges overlapping months... rut-roh
I think I figured out this error: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). I update the field. The trigger is called. The trigger updates the field. The trigger is called. The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called. Repeat as necessary ;) So, I guess my question is what can I put in the trigger to tell it to stop after it updates and not to call itself? 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 2:22:07 PM Thanks Steve :) That was one of the problems. The other was that there was a trigger on the column. I had to remove it before running the update. 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 2:07:35 PM Doh. Try this: update oncall_shifts set shift_start = cast((cast(start_month as varchar) + '/' + cast(start_day as varchar) + '/' + cast(start_year as varchar)) as datetime) sorry about that. -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 29, 2004 1:53 PM To: CF-Talk Subject: RE: Dates - ranges overlapping months... rut-roh Hey Steve! 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 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, -mm-dd); I tried to do: UPDATE ONCALL_SHIFTS SET shift_start = CAST(start_monthstart_daystart_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 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, -mm-dd); CREATE TRIGGER ON table AFTER UPDATE, INSERT BEGIN SET NEW:shift_start = to_date( start_year || '-' || start_month || '-' || start_day, -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]
Re: Dates - ranges overlapping months... rut-roh
Candace Cottrell wrote: Hey Jochem, I am using Transact-SQL Sorry, thought you were on Oracle. The idea should work just the same, but I will leave the exact syntax to people with more T-SQL experience ;-) Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Dates - ranges overlapping months... rut-roh
Nevermind... I found it after I RTFM for almost an hour ;) Here's what you do to turn off Nested Triggers in SQL Server 2000. USE master EXEC sp_configure 'nested triggers', '0' RECONFIGURE WITH OVERRIDE 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 2:42:48 PM I think I figured out this error: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). I update the field. The trigger is called. The trigger updates the field. The trigger is called. The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called.The trigger updates the field. The trigger is called. Repeat as necessary ;) So, I guess my question is what can I put in the trigger to tell it to stop after it updates and not to call itself? 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 2:22:07 PM Thanks Steve :) That was one of the problems. The other was that there was a trigger on the column. I had to remove it before running the update. 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 2:07:35 PM Doh. Try this: update oncall_shifts set shift_start = cast((cast(start_month as varchar) + '/' + cast(start_day as varchar) + '/' + cast(start_year as varchar)) as datetime) sorry about that. -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 29, 2004 1:53 PM To: CF-Talk Subject: RE: Dates - ranges overlapping months... rut-roh Hey Steve! 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 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, -mm-dd); I tried to do: UPDATE ONCALL_SHIFTS SET shift_start = CAST(start_monthstart_daystart_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 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, -mm-dd); CREATE TRIGGER ON table AFTER UPDATE, INSERT BEGIN SET NEW:shift_start = to_date( start_year || '-' || start_month || '-' || start_day, -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
