• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Problem with between date ranges

Explorer ,
Oct 15, 2007 Oct 15, 2007

Copy link to clipboard

Copied

I have to generate and insert a record for each day in a series of date ranges. I am passign some values from a form and trying to insert them for each day.

Here is my code:

<cfquery name="datetest" datasource="ds">

SELECT '#form.building#' AS BldgArea, #form.semcode# AS Semester, DateAdd('d', DaysSpan.Counter, '#form.firstday#') AS SchedDt, DaysSpan.Counter AS Expr1
FROM DaysSpan
WHERE ((DateAdd('d', DaysSpan.Counter, '#form.firstday#')) <= '#form.lastday#')
</cfquery>

So I have a table with a a field called counter which has rows 0 - 366. I want to get a firstday and increment that date by 1 until it reaches the lastday then stop.

I run the page and i am not getting any errors, but nothing is being output. When I take the where clause out it works fine and output 365 days of dates starting with my firstday. I am only going by semester though so I only want the days in between firstday and lastday output.

Any ideas?
TOPICS
Advanced techniques

Views

753

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 15, 2007 Oct 15, 2007

Copy link to clipboard

Copied

Your post does not clearly describe what you are trying to accomplish. Are you attempting to insert a series of records starting with the form.firstday and ending with form.lastday?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Oct 15, 2007 Oct 15, 2007

Copy link to clipboard

Copied

Yes that is what I am trying to do. I am going to be adding a schedule in for each day of a semester. The semester firstday and lastday are passed through the form. I need to get those 2 dates, find every day in between and insert a record for each day with a schedule on it. I do not have the insert statement done at this time. I am merely trying to get and display the proper date range.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 15, 2007 Oct 15, 2007

Copy link to clipboard

Copied

something like this.
ThisDate = ParseDateTime(form.firstdate);
FinalDate = ParseDateTime(form.firstdate);

<cfloop condition = "ThisDate lte FinalDate:>
<cfquery>
insert into yourtable
(fields)
values
(values inlcuding ThisDate variable)
</cfquery>
<cfset ThisDate = DateAdd("d", 1, ThisDate)
</cfloop>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Oct 15, 2007 Oct 15, 2007

Copy link to clipboard

Copied

Thanks I'll give this a try.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Oct 15, 2007 Oct 15, 2007

Copy link to clipboard

Copied

> WHERE ((DateAdd('d', DaysSpan.Counter, '#form.firstday#')) <= '#form.lastday#')

Do you mean something like this..?

<!--- assumes start/end are valid datetime objects --->
SELECT DateAdd('d', DaysSpan.Counter, #yourStartDateTime#)
FROM DaysSpan
WHERE DaysSpan.Counter <= #dateDiff("d", yourStartDateTime, yourEndDateTime)#

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Oct 15, 2007 Oct 15, 2007

Copy link to clipboard

Copied

Yes that is what I am thinking but it has to be something like:


SELECT DateAdd('d', DaysSpan.Counter, #yourStartDateTime#)
FROM DaysSpan
WHERE DateAdd('d', DaysSpan.Counter, #yourStartDateTime#) <= #dateDiff("d", yourStartDateTime, yourEndDateTime)#

DaysSpan.Counter is just a number between 0-366. So that whatever the firstday is it adds the counter to it all the way up to 366 to generate the dates. I want it to stop though when it hits the lastday.



Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Oct 15, 2007 Oct 15, 2007

Copy link to clipboard

Copied

> Yes that is what I am thinking but it has to be something like:

Why?

Did you try it?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Oct 16, 2007 Oct 16, 2007

Copy link to clipboard

Copied

Yes I tried it. I did not get any errors but it did not work as I need it to. Say #firstday# is 01/15/2008. I need to add my dayspan.counter to it to increment the days by one. There are 0-366 numbers it adds to it. So I would get 01/16/2008, 01/17/2008, etc. Then I want to stop it when it reaches like 05/12/2008. When I ran your query I got results that were like 12/31/1899, 01/01/1900.

I need to compare the date values I think, not just the difference between them.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Oct 16, 2007 Oct 16, 2007

Copy link to clipboard

Copied

LATEST
> <!--- assumes start/end are valid datetime objects --->
> When I ran your query I got results that were like 12/31/1899, 01/01/1900.

I suspect you did not parse the form values into datetime objects first, and that's why you're seeing those results. If you just pass in a string like 01/15/2008, the database will treat the value as a numeric date (1 divided by 15 divided by 2008). Resulting in dates like 01/01/1900. It works correctly if you pass in a datetime object.

Take a look at the date functions: CreateDateTime, ParseDateTime, etc... or <cfqueryparam> and its date/time cfsqltypes.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation