How to insert date into db?

blu_sky · blu_sky

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; charset=iso-8859-1" />
<title>test form</title>
</head>

<body>

<p>test form </p>

<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> </p>
</body>
</html>
:confused;

paross1 · paross1

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

SilverStrike · SilverStrike

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>

OldCFer · OldCFer

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.

blu_sky · blu_sky

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. :)

OldCFer · OldCFer

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()

paross1 · paross1

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

blu_sky · blu_sky

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.

How to insert date into db?

Thread Tools

Display

How to insert date into db?

Similar Questions and Discussions

Insert Date headache

insert date into table

ASP SQL Insert NULL Date Value

Date Insert puzzle :: 05/23/76 becomes 12: 12:13

Insert date

Re: How to insert date into db?

Re: How to insert date into db?

Re: How to insert date into db?

Re: How to insert date into db?

Re: How to insert date into db?

Re: How to insert date into db?

Re: How to insert date into db?

Posting Permissions