convert a datetime to smalldatetime

[sparks]

Is there any way to do this.
I checked and all my datetimes are > 1/1/1900 so that is not the
problem.

I do not know how to do this. Just for a test I I even converted the
datetime to date but it didnt' work either.

[Erland Sommarskog]

Post by sparks
Is there any way to do this.
I checked and all my datetimes are > 1/1/1900 so that is not the
problem.
I do not know how to do this. Just for a test I I even converted the
datetime to date but it didnt' work either.

It's utterly unclear what you want to do. I make a guess that you want to
change the data type of a table column. Try:

ALTER TABLE tbl ALTER COLUMN col smalldatetime NOT NULL

If that does not help, please be more specific, and include any
error message you get.

[sparks]

sorry this is exactly what I tried


ALTER TABLE tblCustomerInformation ALTER COLUMN DateOfIssue
smalldatetime NULL

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


date conversion error
Msg 242, Level 16, State 3, Line 3
The conversion of a date data type to a smalldatetime data type
resulted in an out-of-range value.

I have tried this with datetime and date in the DateOfIssue column
both times I get the same error

I checked to make sure none of these dates where less than 1/1/1900
and the oldest record is 7/15/2001

[Erland Sommarskog]

Post by sparks
ALTER TABLE tblCustomerInformation ALTER COLUMN DateOfIssue
smalldatetime NULL
datetime conversion error
Msg 242, Level 16, State 3, Line 3
The conversion of a datetime data type to a smalldatetime data type
resulted in an out-of-range value.
The statement has been terminated.
date conversion error
Msg 242, Level 16, State 3, Line 3
The conversion of a date data type to a smalldatetime data type
resulted in an out-of-range value.
I have tried this with datetime and date in the DateOfIssue column
both times I get the same error
I checked to make sure none of these dates where less than 1/1/1900
and the oldest record is 7/15/2001

And SELECT MAX(datetimecol) returns?

[sparks]

THANK YOU so much.

I just never thought of looking for that.

lets see in one year they have clients from 2050 to 9999
9 records out of 236

and one from 0916 lol



On Mon, 14 May 2012 22:25:59 +0200, Erland Sommarskog

Post by Erland Sommarskog
SELECT MAX(datetimecol)