Professional Web Applications Themes

Date quiry by month. - Coldfusion - Advanced Techniques

On my access data base, i've got date in "01-Jan-05" format. Now i do need to quiry that sort the data by month, i'.e Jan-05 Feb-05 ect. I hope that some one can help me with ssuth a quiry...

Sponsored Links
  1. #1

    Default Date quiry by month.

    On my access data base, i've got date in "01-Jan-05" format.
    Now i do need to quiry that sort the data by month, i'.e Jan-05 Feb-05 ect.
    I hope that some one can help me with ssuth a quiry
    Sponsored Links
    fluiter Guest

  2. #2

    Default Re: Date quiry by month.

    SELECT MONTH(updatedate) AS months
    FROM temployee
    ORDER BY months DESC
    jorgepino Guest

  3. #3

    Default Re: Date quiry by month.

    If you are dealing with a date/time column, can't you just order by that column?

    SELECT yourDateTimeColumn
    FROM yourTable
    ORDER BY yourDateTimeColumn
    mxstu Guest

  4. #4

    Default Re: Date quiry by month.

    What for statement should i use for (updatedate) as i do get a not defined
    error.

    What i would like see as output
    Jan-05
    Feb-05
    Etc it should strip de day date from de database date (01-)jan-05

    fluiter Guest

  5. #5

    Default Re: Date quiry by month.

    1. What is the data type of your column?
    2. Please post some code
    mxstu Guest

  6. #6

    Default Re: Date quiry by month.

    Here is the cose i'm having sofar.
    My date type is midlong only dd-mmm-yy, no time attached.


    SELECT * FROM tbl_issue WHERE date_issue BETWEEN
    <cfqueryparam value='#Form.date_start#'cfsqltype="cf_sql_date">
    AND
    <cfqueryparam value='#Form.date_end#' cfsqltype="cf_sql_date">
    AND region = '#FORM.region#'
    AND assigned_email LIKE '%#FORM.assigned_email#%'
    AND originator_email LIKE '%#FORM.originator_email#%'
    <cfif trim(form.date_closed) eq "closed">
    AND date_closed IS NOT NULL
    <cfelseif trim(form.date_closed) eq "open">
    AND date_closed IS NULL
    </cfif>
    </cfquery>

    fluiter Guest

  7. #7

    Default Re: Date quiry by month.

    > Now i do need to quiry that sort the data by month
    If you want to sort the records, add an ORDER BY clause to your query. If not, what exactly do you mean by "sort"?


    mxstu Guest

  8. #8

    Default Re: Date quiry by month.

    Forgive me using "Sort"
    On the report lines i do not want to show the date being 01-jan-05 ,but just
    jan-05.
    I might sound stupid just stripping the day date, but i've to do this because
    of legal obligations.


    fluiter Guest

  9. #9

    Default Re: Date quiry by month.

    If you just want to format the date displayed in your report, you should be
    able to use the CF DateFormat() function. Pass the function a date/time object
    and a "mask" ( i.e how it should be formatted. for example "mmm-yy").

    <cfoutput query="yourQuery">
    #DateFormat(yourQuery.someDateTimeColumn, "mmm-yy")#<br>
    </cfoutput>

    If you need to group the output, then you would probably need to use your
    database's date functions in your query.

    mxstu Guest

  10. #10

    Default Re: Date quiry by month.

    Thanks for your help.


    fluiter Guest

Similar Threads

  1. Start and end date of month
    By ccnorris in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: July 19th, 08:46 PM
  2. Query distinct month and date
    By LKnomad in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 12th, 08:32 PM
  3. Rounding a Date to the 1st Day of the month
    By Mikey in forum FileMaker
    Replies: 2
    Last Post: February 24th, 03:12 PM
  4. Last Date of previous month
    By navdeep virk in forum Informix
    Replies: 9
    Last Post: November 11th, 02:47 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