Unable to update the datetime column

Question

Hi

If I try to run a simple UPDATE query

 I've checked the Date Time to be exactly the same in Regional Settings on the Server and am now very stuck if anyone can help please :

UPDATE COMMS SET TX_DATETIME='23/04/2012' WHERE ID = '18928'
--Then this error appears :-
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Answer 1

Jmatty2000

Try

 

UPDATE COMMS SET TX_DATETIME=convert(date,'23/04/2012') WHERE ID = '18928'

or 

UPDATE COMMS SET TX_DATETIME=convert(date,'2012/04/23') WHERE ID = '18928'

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

hi Manish

If I try this I get error.  :-

Type date is not a defined system type.

Also I should have said but the SQL command cannot change as this is what is generated as standard by a Stored Procedure so the fault must be on the server setting somewhere I think

Any more ideas plesae.

thanks

Matthew

Answer 3

Matthew,

Could you please tell us the output of the following command in your system.

 

select getdate()

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

Hi Manish

No problem. It outputs

2012-04-23 13:40:04.557

but this is the same kind of format that a 'working' server' also outputs.
Any more help?

Answer 5

hi Manish

If I try this I get error.  :-

Type date is not a defined system type.

Also I should have said but the SQL command cannot change as this is what is generated as standard by a Stored Procedure so the fault must be on the server setting somewhere I think

Any more ideas plesae.

thanks

Matthew

If SQL Server says DATE isn't a valid datatype then you're using SQL Server <=2005. DATE is only valid for >= SQL Server 2008.

The reason why SQL Server doesn't like your datetime value is because your value is out of range (i.e. the date does not exist). Use SET DATEFORMAT for the correct local format used. Currently there is no 23rd month.

Given your current format I would use:

CONVERT(DATETIME, 'YYYY/MM/DD')

SET DATEFORMAT: http://msdn.microsoft.com/en-us/library/ms189491.aspx

DATETIME DataType: http://msdn.microsoft.com/en-us/library/ms187819.aspx

ISO 8601: http://en.wikipedia.org/wiki/ISO_8601

-Sean

Answer 6

Hi

Your right the SQL version is :-

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

but this is the same version as many other 'working servers'. Is there any server setting that can be adjusted to fix this perhaps?

Answer 7

Hello,

Try this:

SET DATEFORMAT dmy;
GO

UPDATE COMMS SET TX_DATETIME='23/04/2012' WHERE ID = '18928';
GO

-Sean

Answer 8

Thanks Sean - thats really helpful.

Anyone know how this problem has just happened? The server has not had any new Stored Procedures so its seems a mystery how this problem has just started to happen

Answer 9

Hello,

I'm not sure *HOW* it happened, you'll have to investigate that. As to *WHY* it happened is because (as referenced in my first response to you with links. HINT: Read the links) the format of the date was incorrect to what the DATEFORMAT option was. Either the date values needed to be changed in the query or the DATEFORMAT option needed changed to reflect the update statement.

-Sean

Answer 10

Hi everyone

I've found two reasons why I had this problem

1) I was logged into MS SQL Studio Manager as a Windows user rather than the correct user which had different date format set up for them.

2) The reason why this problem surfaced (in the first place) was that there was a database table index corruption which was found using the DBCC table integrity SQL command. A repair on the table index was made to fix all the problems. This error was returned back when running DBCC was :- Internal Query Processor Error: The query processor encountered an unexpected error during execution

Thanks to everyone who tried to help but it was a good learning experience for me and hope for some others out there as well.

Cheers

Matthew

Answer 11

whenever hardcoding a datetime as a string which gets converted later back to datetime, I recommand to use ISO 8601 date time format as that format is always correctly converted by SQL Server from a string to a datetime independent on any regional settings

see ISO 8601 Format

The advantage in using the ISO 8601 format is that it is an international standard. Also, datetime values that are specified by using this format are unambiguous. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.