SQL Update date/time field issues
Posted on 2011-09-27
I'm currently widening my skill set from MS Access to SQL and hoping to move further on to vb.net, ado.net etc.
As part of this, I've moved the backend of our internal Access DB to SQL Server, and it's mostly functional (I'm still using bound tables at the moment).
I'm having problems with an UPDATE statement. I have in fact removed this completely from Access now, and I'm trying to execute it directly from the SQL server management environment.
I'm attempting to update a date/time field in a table.
The SQL I started with is as follows:
UPDATE [JJSQL].[dbo].[LBEJobs] SET [LBEJobs].[Priority_id] = 2, [LBEJobs].[To_complete_by] = 27/09/2011 10:27:56 WHERE [LBEJobs].[Job_id]=16172
That's the statement, word for word (I can't see any reason to hide names as this is internal/testing only).
I've had a look around, and found that I probably need to use the Convert function, so:
UPDATE [JJSQL].[dbo].[LBEJobs] SET [LBEJobs].[Priority_id] = 2, [LBEJobs].[To_complete_by] = Convert(VarChar,'27/09/2011 10:27:56',120) WHERE [LBEJobs].[Job_id]=16172
However, that brings back the error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
But if I execute the statement:
SELECT Convert(VarChar,'27/09/2011 10:27:56',120)
This does bring back a single field containing the date, as I'd expect.
The field I'm inserting in to, 'To_Complete_by' (not named by me I'll hasten to add) is a 'datetime' format.
I'm quite new to SQL, so the solution might be simple. Help!