SQL Server Conversion of a date data types to a smalldatetime data type resulted in an out of range value

lyfkaqu1  于 2023-08-02  发布在  其他
关注(0)|答案(4)|浏览(113)

I was trying to insert a date type columns but I'm getting an error mentioned in title

insert into table (Date)
select date from dbo.source

The date in dbo.source is of date data type and Date in Table is of smalldatetime data type, so when I try to insert i'm getting error as mentioned in title. I try to use convert(smalldatetime,date) , but still I'm facing an issue. Anyone can help on this?

l7mqbcuq

l7mqbcuq1#

You probably have dates in your table out of this range 1900-01-01 through 2079-06-06, which is for smalldatetime. Date has a range of 0001-01-01 through 9999-12-31. You have to cap the dates to fit into smalldatetime.

swvgeqrz

swvgeqrz2#

I think in your dbo.source table there are records which is out of range of smalldatetime range. Try this to find such out-of-range records -

select date
from dbo.source
where date < '1900-01-01' or date > '2079-06-06'

SmallDateTime range - 1900-01-01 through 2079-06-06

Date range - 0001-01-01 through 9999-12-31

y1aodyip

y1aodyip3#

Either insert NULL for the values that don't fit:

insert into table (Date)
select try_cast(date as smalldatetime) from dbo.source

Or don't insert these rows at all:

insert into table (Date)
select date from dbo.source
where try_cast(date as smalldatetime) is null
fcy6dtqo

fcy6dtqo4#

Please, try with below query with range of date time datatype:

insert into table (Date)
   select  date from dbo.orders
     where date between '1900-01-01 00:00:00' and '2079-06-06 23:59:00'

相关问题