datetime to smalldatetime

I need to convert a datetime field to smalldatetime.

This particular field we only care about the time portion (an example would be '1899-12-30 13:15:00.000')

For now I created another field say 'newTime' that is smalldatetime, in which I want to "update" to the smalldatetime version of the data. I know this will truncate the ms, but I don't care about that. Also the min date that can be used with smalldatetime is Jan 1 1900.

Not sure how to go about doing this.

you could use the CONVERT function. check out BOL for CONVERT functions.

sample:

SELECTCONVERT(varchar,getdate(), 101)

|||

So lets say I have 2 fields, "oldTime" and "newTime"

oldTime is a datetime data type

newTime is a smalldatetime data type

I want to run a query like

update myTableset newTime = oldTimewhere ...etc...

I get this error

Msg 298, Level 16, State 1, Line 1

The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.

Then I ran:

update myTable
set newTime =
(SELECTCONVERT(varchar, oldTime, 101))

I get the error:

Msg 296, Level 16, State 3, Line 1

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

The statement has been terminated.

I'm sure this is because of the date portion in oldTime is < 1900

Would dateadd(dd, 1, oldTime) be be the best way to go about this?

|||

you are probably better off using a VARCHAR instead of smalldatetime. If you have to use smalldatetime, then you cannot put in values with YEAR < 1900. Or you could use one of the CONVERT functions to put only the time part.

for example:

DECLARE @.tsmalldatetime, @.t2datetimeSET @.t2 ='Jul 19 1800 1:14PM'SET @.t =convert(varchar,@.t2,114)PRINT @.t