Inserting datetime using cfqueryparam in a SQL_Server database.

Hi

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

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.

You can try any one method :-

1> Use cfsqltype="cf_sql_vachar"

or cfsqltype="cf_sql_timestamp"

2> Add the following to the connection parameters of the datasource

DateTimeInputParameterType=dateTime

Thanks

VJ