Out Of Range Date Type

[mentasses]

I have a simple update query:

UPDATE SERVICES.dbo.MASTER
SET DATEOFLEAVING = CONVERT(Char,b.DATELEAVE,103)
FROM SERVICES.dbo.MASTER a JOIN SERVICES.dbo.EXITINTERVIEW b
ON a.STAFFNO = b.STAFFNO

When I run the query I receive the error message:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Can anyone help?

 

[vongrunt]

And data type of column DATEOFLEAVING is... ?

 

[mentasses]

The datatype should be date format but I think some data has been entered as text.
I do not know how to ensure the datatype is correct before running the update?

 

[vongrunt]

Got a little bit confused... if dateofleaving is of type date, they why CONVERT(char... )?

 

[mentasses]

I need the date as DD/MM/YYYY
Although if I just use
SET DATEOFLEAVING = DATELEAVE
I still get the same error message

 

[vongrunt]

Argh... then DATEOFLEAVING is date and DATELEAVE char/varchar, right?

If true, then some of DATELEAVE values may be out of range (year below 1753) or invalid (Feb 30th or something). To check that, use ISDATE() function. Also: you can use SET DATEFORMAT dmy before UPDATE statement but this is maybe not necessary.

 

[mentasses]

It appears that both DATELEAVE and DATEOFLEAVING have some date and some varchar data.
Is their a way I can convert them all to date format before I run the update?

 

[dbomrrsm]

DATELEAVE and DATEOFLEAVING need to be a defined data type in the table - each of them have there own type - which one is datetime and which one is varchar/char.



[blue]DBomrrsm[/blue]

 

[SQLBill]

Run a query on the column using ISDATE.

For example:

SELECT ISDATE(DATELEAVE)
FROM ExitInterview

Refer to the BOL for more information on ISDATE.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875