MSSQL Insert Query

obrienkev

Hi all,

Can someone please explain to me what way SQL Server is setup in respect to data types.

This insert query won't work. Gives 'Final Query Error'. Any ideas?

$query = "INSERT INTO meeting(location_ID, meeting_date, meeting_start, meeting_end, booked_by, note) ".
 "VALUES('$location', '$meeting_date', '$time_start', '$time_end', '$booked_by', '$note' )";

echo $query;

$final_result = mssql_query($query) or die('Final Query Error');

Echoing gives this..
INSERT INTO meeting(location_ID, meeting_date, meeting_start, meeting_end, booked_by, note) VALUES('1', '13/02/2006', '13/02/2006 11:00:00', '13/02/2006 13:15:00', 'ff20946', 'testing...' )

meeting_date is setup as smalldatetime type
meeting_start, meeting_end are setup as datetime type

Comparing with dates/times stores in MSSQL Server is confusing me.

Thanks again.

Kevin.

Roger_Ramjet

Yes, mssql date types are a mess - everyone else has had this worked out for ages.
Of course, your locale and language settings are going to effect how dates are interpretted or displayed, but some sort of standard should apply to I/O and queries.

The only thing that is certain from sql server books online is that you should just always use the ISO 8601 format 'yyyy-mm-dd' with ODBC, ADO and OLE DB.

It says that for other APIs and transact sql you should use 'yyyymmdd'. Great stuff, another non-portable trap built into the product.

The final option is that you should use CONVERT() in stored procs and triggers to format a date string to a date/time data type with an explicit language style parameter. This last makes your code language independent.

Looking it over, the convert() option seems to be one that will work in any context, ODBC, stored proc, transact sql, or OLE DB; but I would want to do lots of testing before I relied on that assertion.

edwardsbc

If strtotime() can figure out '13/02/2006 11:00:00' (I haven't tried it), then the following would convert your given format into a suitable SQL timestamp format:

$time_start_sql=date('Y-m-d H:i:s',strtotime($time_start));

strtotime() is a pretty smart function, I recommend trying this out. If it works it is a simple solution.