Professional Web Applications Themes

Start and end date of month - Coldfusion - Advanced Techniques

I am trying to calculate the start and end dates for a month. I am using the formulas: <cfset first_of_month = #CreateODBCDate(CreateDate(Year(Now()),Month(Now() ),1))#> <cfset last_of_month = #CreateODBCDate(CreateDate(Year(Now()),Month(Now() ),DaysInMonth(Now())))#> When I try to insert the data into the database I get the following error message: "ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{d '2005-07-01'}''." Any help with this will be much appreciated!...

  1. #1

    Default Start and end date of month

    I am trying to calculate the start and end dates for a month. I am using the
    formulas:

    <cfset first_of_month =
    #CreateODBCDate(CreateDate(Year(Now()),Month(Now() ),1))#>
    <cfset last_of_month =
    #CreateODBCDate(CreateDate(Year(Now()),Month(Now() ),DaysInMonth(Now())))#>

    When I try to insert the data into the database I get the following error
    message:
    "ODBC Error Code = 37000 (Syntax error or access violation)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
    query expression ''{d '2005-07-01'}''."

    Any help with this will be much appreciated!

    ccnorris Guest

  2. #2

    Default Re: Start and end date of month

    Can you post your insert query?
    mxstu Guest

  3. #3

    Default Re: Start and end date of month

    Sure. This is only part of it. I am just using this part for now to test the
    new field names.

    <cfquery name="newdata2" datasource="DBSales">
    INSERT INTO moinprogress(month_num,
    first_of_month,
    last_of_month,
    1)
    VALUES('#month_num#',
    '#first_of_month#',
    '#last_of_month#',
    '#DTOTAL#')
    </cfquery>

    ccnorris Guest

  4. #4

    Default Re: Start and end date of month

    Just get rid of the single quotes around the date variables:

    <cfquery name="newdata2" datasource="DBSales">
    INSERT INTO moinprogress(month_num,
    first_of_month,
    last_of_month,
    1)
    VALUES('#month_num#',
    '#first_of_month#',
    '#last_of_month#',
    '#DTOTAL#')
    </cfquery>

    mxstu Guest

  5. #5

    Default Re: Start and end date of month

    Thanks! I should have tried that. I was making it harder than it really was. Thanks again!!
    ccnorris Guest

Similar Threads

  1. Query distinct month and date
    By LKnomad in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 12th, 08:32 PM
  2. SQL: can a date ONLY contain month/year info?
    By darrel in forum Dreamweaver AppDev
    Replies: 4
    Last Post: March 28th, 02:53 PM
  3. 2 digit Date and month format
    By tony in forum ASP
    Replies: 3
    Last Post: January 30th, 10:48 AM
  4. Last Date of previous month
    By navdeep virk in forum Informix
    Replies: 9
    Last Post: November 11th, 02:47 PM
  5. selecting based on a month in a date
    By Creative Solutions New Media in forum PHP Development
    Replies: 0
    Last Post: August 28th, 10:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139