Professional Web Applications Themes

Date Format Insert/Display Help - Coldfusion - Advanced Techniques

hi, I'm having some problems with the Date Format and how its displayed using SQL and CF. When inserting the Date into the Oracle Database using CF, it has to be entered 2-DEC-1985 when it gets displayed in a table, it comes out like this 1985-12-2 00:00:00.0 with the time at the end, and the format totally differnet to the input. I would like to have a a simplified way of entering the Date into the database, and also a nice way of displaying it as well.i am currently displaying it in a simple table. the Field is set as ...

  1. #1

    Default Date Format Insert/Display Help

    hi,

    I'm having some problems with the Date Format and how its displayed using SQL
    and CF.

    When inserting the Date into the Oracle Database using CF, it has to be
    entered 2-DEC-1985

    when it gets displayed in a table, it comes out like this

    1985-12-2 00:00:00.0

    with the time at the end, and the format totally differnet to the input.

    I would like to have a a simplified way of entering the Date into the
    database, and also a nice way of displaying it as well.i am currently
    displaying it in a simple table.

    the Field is set as a Date field in the Oracle database, but im not sure what
    the input type is supposed to be with the HTML (Text??)??

    thanks in advance

    bluebirds1984 Guest

  2. #2

    Default Re: Date Format Insert/Display Help

    It's inserting correctly in the database. It's an odbc dat object.

    You can change the dispaly of the date by using dateformat fucntion.

    Like: dateformat(mydate, "mm/dd/yyyy")
    dallaswebb Guest

  3. #3

    Default Re: Date Format Insert/Display Help

    This statement is false:
    When inserting the Date into the Oracle Database using CF, it has to be
    entered 2-DEC-1985

    There is more than one way to do this, but the method I use is (for today's
    date)
    {d '2005-11-21'}

    Cold Fusion also has functions called createodbcdate and createodbcdatetime
    which will probably also get the job done.

    Once you have the date in, when you select it, the dateformat function allows
    you to display it any way you want.


    Originally posted by: bluebirds1984
    hi,

    I'm having some problems with the Date Format and how its displayed using SQL
    and CF.

    When inserting the Date into the Oracle Database using CF, it has to be
    entered 2-DEC-1985

    when it gets displayed in a table, it comes out like this

    1985-12-2 00:00:00.0

    with the time at the end, and the format totally differnet to the input.

    I would like to have a a simplified way of entering the Date into the
    database, and also a nice way of displaying it as well.i am currently
    displaying it in a simple table.

    the Field is set as a Date field in the Oracle database, but im not sure what
    the input type is supposed to be with the HTML (Text??)??

    thanks in advance



    Dan Guest

  4. #4

    Default Re: Date Format Insert/Display Help

    Thank you dallasweb your suggestion works. i now have a table displayed with
    the a nicer date format using

    #DateFormat(DOB, "dd-mm-yyyy")#

    i now just have a little trouble when updating this information. when i want
    to update a record, i have the current data displayed in the input dialog using
    the following code.

    <INPUT TYPE="text" NAME="DOB" VALUE = "#DateFormat(DOB, "dd-mm-yyyy")#"><BR>

    this displays the date nicely in the field. but the problem I am having, is
    that the Oracle Database does not accept the values displayed. Oracle wants the
    date shown in the 12-DEC-1984 Format for example. i get the error message...

    [Macromedia][Oracle JDBC Driver][Oracle]ORA-01843: not a valid month


    is there a way I can have the date entered in the format of "dd-mm-yyyy"
    converted so the Oracle Database will accept it??

    again thanks for your help.

    bluebirds1984 Guest

  5. #5

    Default Re: Date Format Insert/Display Help

    Yes, choose yyyy-mm-dd as your input format. Or use createodbcdate. As long
    as the data gets inserted, it doesn't matter what the format was.

    Originally posted by: bluebirds1984
    Thank you dallasweb your suggestion works. i now have a table displayed with
    the a nicer date format using

    #DateFormat(DOB, "dd-mm-yyyy")#

    i now just have a little trouble when updating this information. when i want
    to update a record, i have the current data displayed in the input dialog using
    the following code.

    <INPUT TYPE="text" NAME="DOB" VALUE = "#DateFormat(DOB, "dd-mm-yyyy")#"><BR>

    this displays the date nicely in the field. but the problem I am having, is
    that the Oracle Database does not accept the values displayed. Oracle wants the
    date shown in the 12-DEC-1984 Format for example. i get the error message...

    [Macromedia][Oracle JDBC Driver][Oracle]ORA-01843: not a valid month


    is there a way I can have the date entered in the format of "dd-mm-yyyy"
    converted so the Oracle Database will accept it??

    again thanks for your help.



    Dan Guest

Similar Threads

  1. Date display format
    By erickia in forum Dreamweaver AppDev
    Replies: 16
    Last Post: May 17th, 07:18 PM
  2. Date Format Problem - SQL Server Insert From Web Application
    By Steve in forum ASP.NET Web Services
    Replies: 8
    Last Post: May 1st, 02:37 PM
  3. converting date into database date format(newbie)
    By Binay Agarwal in forum PHP Development
    Replies: 2
    Last Post: August 29th, 01:09 PM
  4. format file and bcp/bulk insert
    By Robert Taylor in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 7th, 11:26 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