Professional Web Applications Themes

DateFormat in cfquery - Coldfusion - Getting Started

Hi. Im trying to changing DateFormat in a cfquery and I cant get it right. Is something like this possible. (I always get an error saying "DateFormat is not a recognized function name") <cfquery name="myName" datasource="myDatasource"> SELECT DateFormat(EinstkDags, 'yyyymmdd') AS myDate FROM dbo.myTable WHERE myDate >= 20050825 AND myDate <= 20050902 ORDER BY myDate DESC </cfquery> Sveinbjorn....

  1. #1

    Default DateFormat in cfquery

    Hi.
    Im trying to changing DateFormat in a cfquery and I cant get it right.

    Is something like this possible. (I always get an error saying "DateFormat is
    not a recognized function name")

    <cfquery name="myName" datasource="myDatasource">
    SELECT DateFormat(EinstkDags, 'yyyymmdd') AS myDate
    FROM dbo.myTable
    WHERE myDate >= 20050825 AND myDate <= 20050902
    ORDER BY myDate DESC
    </cfquery>

    Sveinbjorn.

    EINN Guest

  2. #2

    Default Re: DateFormat in cfquery

    Format the date when you output the query ... functions in a query need to be
    database functions recognized by the database server, not CF, and I don't think
    dateformat is one. The error message is from the data server, not actually CF.





    SafariTECH Guest

  3. #3

    Default Re: DateFormat in cfquery

    Ok, I understand.
    But how can I solve this issue?
    What I am trying to do, is to let users select dates using dates format like
    20050829 but the date value on my database are like '2005-09-02 19:54:35'
    Is there a way to solve this without canging the dateformat on my SQL database?

    EINN Guest

  4. #4

    Default Re: DateFormat in cfquery

    Convert the date they input to ODBC format using CreateDate and CreateODBCdate
    (depending on what the user is supplying you may only need the latter) - what
    you see in the database is just a representation of what it shows based on a
    selected format, not what the date actually is.

    All you are doing in the query is asking for a date between those 2
    parameters. So long as the field in the DB is actually a date field, and the
    format used in the BETWEEN statement is a valid date format, then it will still
    pull everything related to those dates.

    DateFormat only comes into play when you visually want to change what the user
    sees and is only necessary in the output.




    <cfquery name="myName" datasource="myDatasource">
    SELECT myDate
    FROM dbo.myTable
    WHERE myDate >= 20050825 AND myDate <= 20050902
    ORDER BY myDate DESC
    </cfquery>

    <CFOUTPUT query="myName">#DateFormat(myDate,"yyyymmdd")#<br> </CFOUTPUT>

    SafariTECH Guest

  5. #5

    Default Re: DateFormat in cfquery

    Thank you for quick replay

    But I get this error for my query:
    Error Executing Database Query.
    [Macromedia][SQLServer JDBC Driver][SQLServer]Arithmetic overflow error
    converting expression to data type datetime.

    Sveinbjorn


    EINN Guest

  6. #6

    Default Re: DateFormat in cfquery

    That would then seem to imply that the 20050825 layout is not a valid date
    format

    what happens if you use a 2005-08-25 layour for your dates?

    The error is from SQL Server itself, so that is the area we need to figure out
    that best suits your needs.

    SafariTECH Guest

  7. #7

    Default Re: DateFormat in cfquery

    Thats it!

    Many thanks!

    Sveinbjorn
    EINN Guest

  8. #8

    Default Re: DateFormat in cfquery

    >the date value on my database are like '2005-09-02 19:54:35'

    EINN,

    Since your field has both a date and time, make sure your sql statement takes
    the "time" into account. This statement here

    WHERE myDate BETWEEN '2005-08-25' AND '2005-09-02'

    would NOT include a record with the date '2005-09-02 19:54:35' . To include
    records dated any time on 09/02/2005, try using something like this:

    WHERE myDate >= '2005-08-25' AND myDate < '2005-09-03'

    Translated this "basically" requests all records with a "date" between
    08/25/2005 midnight and 09/02/2005 11:59:59:59. PM.

    mxstu Guest

Similar Threads

  1. Add To Dateformat()
    By bweno in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 29th, 01:22 AM
  2. Dateformat question
    By Steve Grosz in forum Macromedia ColdFusion
    Replies: 2
    Last Post: May 25th, 12:32 AM
  3. DateFormat and cfcontent problem
    By dmx1999 in forum Macromedia ColdFusion
    Replies: 5
    Last Post: May 9th, 08:37 PM
  4. dateformat - month
    By Mattastic in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: April 21st, 03:05 PM
  5. DateFormat?
    By RP in forum ASP
    Replies: 1
    Last Post: July 8th, 07:50 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