Firstname:
lastname:
" />

 

:confused; [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => blu_sky [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> How to insert date into db? - Coldfusion Database Access

How to insert date into db? - Coldfusion Database Access

I've been fighting with this simple (?) issue all weekend long and have gotten nowhere. I want to have the current date and time inserted into the _date field of the database when the form is submitted. As seen in the code below, I have set the form's _date field as a "hidden", and the mysql db _date field is set to "DateTime". What's wrong with this code? The form code: <cfset CurrentPage=GetFileFromPath(GetTemplatePath())> <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1"> <cfquery datasource="testdb"> INSERT INTO fahtest (firstname, lastname, _date) VALUES ( <cfif IsDefined("FORM.firstname") AND #FORM.firstname# NEQ ""> '#FORM.firstname#' <cfelse> NULL </cfif> , ...

  1. #1

    Default How to insert date into db?

    I've been fighting with this simple (?) issue all weekend long and have gotten
    nowhere. I want to have the current date and time inserted into the _date field
    of the database when the form is submitted. As seen in the code below, I have
    set the form's _date field as a "hidden", and the mysql db _date field is set
    to "DateTime". What's wrong with this code?


    The form code:

    <cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
    <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
    <cfquery datasource="testdb">
    INSERT INTO fahtest (firstname, lastname, _date) VALUES (
    <cfif IsDefined("FORM.firstname") AND #FORM.firstname# NEQ "">
    '#FORM.firstname#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.lastname") AND #FORM.lastname# NEQ "">
    '#FORM.lastname#'
    <cfelse>
    NULL
    </cfif>
    ,
    '#FORM._date#'
    )
    </cfquery>
    <cflocation url="confirm_test.cfm">
    </cfif>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; cht=iso-8859-1" />
    <title>test form</title>
    </head>

    <body>

    <p>test form </p>
    <!--- <form name="form1" id="form1" method="POST"
    action="<cfoutput>#CurrentPage#</cfoutput>"> --->
    <form name="form1" id="form1" method="POST"
    action="<cfoutput>#CurrentPage#</cfoutput>">
    <table width="52%" border="0" cellspacing="0" cellpadding="0">
    <tr>
    <td width="30%"><div align="right">Firstname:</div></td>
    <td width="70%"><input name="firstname" type="text" id="firstname"
    /></td>
    </tr>
    <tr>
    <td><div align="right">lastname:</div></td>
    <td><input name="lastname" type="text" id="lastname" /></td>
    </tr>
    <tr>
    <td><div align="right"></div></td>
    <td><input type="submit" value="Submit" /></td>
    </tr>
    </table>

    <input type="hidden" name="MM_InsertRecord" value="form1">
    <input name="_date" type="hidden" id="_date"
    value="<cfoutput>#DateFormat(now(),"dd/mm/yyyy")#</cfoutput>" />
    </form>
    <p>&nbsp;</p>
    </body>
    </html>
    :confused;

    blu_sky Guest

  2. #2

    Default Re: How to insert date into db?

    Unless there is a time difference between the ColdFusion server and the
    database server, why don't you just use the '"current date" function that is
    native to your particular database, such as GETDATE() for MSSQL, NOW() for
    Access or MySQL, SYSDATE for Oracle, etc.

    INSERT INTO your_table (date_column) VALUES (now())
    -or-
    INSERT INTO your_table (date_column) VALUES (getdate())
    -or-
    INSERT INTO your_table (date_column) VALUES (sysdate).....etc.

    Phil

    paross1 Guest

  3. #3

    Default Re: How to insert date into db?

    When entering date into the DB, you need to use the CreateODBCDateTime().

    Example:
    <cfquery datasource="testdb">
    INSERT INTO fahtest (firstname, lastname, _date)
    VALUES (
    <cfif IsDefined("FORM.firstname") AND #FORM.firstname# NEQ
    "">'#FORM.firstname#'<cfelse>NULL</cfif>,
    <cfif IsDefined("FORM.lastname") AND #FORM.lastname# NEQ
    "">'#FORM.lastname#'<cfelse>NULL</cfif>,
    #CreateODBCDateTime(Now())#
    )
    </cfquery>

    SilverStrike Guest

  4. #4

    Default Re: How to insert date into db?

    A word of caution. I see you're using day first notation. Unless you set the
    locale properly, and
    use the LS date functions for handling them, CF will interpret them as month
    first when the days
    are 12 or less.

    OldCFer Guest

  5. #5

    Default Re: How to insert date into db?

    Thanks a bunch for the input, guys! I finally got it to work using the now()
    function by itself, although I would much rather have the date inserted /
    displayed as "mm/dd/yyyy". I tried using CreateODBCDate(DateFormat(now(),
    "mm/dd/yyyy")), but that didn't change it. Is there another way to do this? I
    REALLY do appreciate all of the help. Thanks again. :)

    blu_sky Guest

  6. #6

    Default Re: How to insert date into db?

    If you use CreateODBCDate(Now()), it will be inserted as a date with no time
    i.e. 00:00:00
    for time. You can't format the way a date is stored, only how it's displayed.
    Once you pull
    it from the DB you can display it any way you want using DateFormat()

    OldCFer Guest

  7. #7

    Default Re: How to insert date into db?

    When you say "I would much rather have the date inserted / displayed as
    "mm/dd/yyyy", you have to understand that date/time columns in databases are
    not stored as text values, but some sort of number representing a specific
    amount of time since a specific point in time. like so many milliseconds since
    1900, etc..

    I don't know about MySQL, but in SQL Server, for instance, "Values with the
    datetime data type are stored internally by Microsoft SQL Server as two 4-byte
    integers. The first 4 bytes store the number of days before or after the base
    date, January 1, 1900. The base date is the system reference date. The other 4
    bytes store the time of day represented as the number of milliseconds after
    midnight." Also, "SQL Server stores smalldatetime values as two 2-byte
    integers. The first 2 bytes store the number of days after January 1, 1900. The
    other 2 bytes store the number of minutes since midnight."

    So, you see, your date and time are not stored as MM/DD/YYYY unless you use
    VARCHAR (character) type column instead of a date/time or timestamp. However,
    if you use a character type column, you then make it very difficult to do date
    and time "math", and date column sorting becomes difficult, etc.

    Phil

    paross1 Guest

  8. #8

    Default Re: How to insert date into db?

    Thank you all very much for shedding light on some of the foggy areas (nuances)
    of CF. As a beginner, let me say, you have shaved a lot of time off of the
    tedium and frustration of trying to figure it out by oneself. Thanks again.

    blu_sky Guest

Similar Threads

  1. Insert Date headache
    By wtodd in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: July 15th, 02:50 PM
  2. insert date into table
    By JoeyTMann in forum Coldfusion Database Access
    Replies: 10
    Last Post: March 22nd, 05:19 PM
  3. ASP SQL Insert NULL Date Value
    By Chad S in forum ASP
    Replies: 5
    Last Post: February 19th, 07:33 PM
  4. Replies: 16
    Last Post: September 2nd, 06:53 PM
  5. Insert date
    By Wayne Morgan in forum Microsoft Access
    Replies: 1
    Last Post: July 13th, 12:55 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