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)|浏览(114)

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?

sql-server

来源：https://stackoverflow.com/questions/60092374/conversion-of-a-date-data-types-to-a-smalldatetime-data-type-resulted-in-an-out

4条答案

按热度按时间

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.

赞(0）回复(0）举报 2023-08-02

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

赞(0）回复(0）举报 2023-08-02

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

赞(0）回复(0）举报 2023-08-02

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'

赞(0）回复(0）举报 2023-08-02

我来回答

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

4条答案

相关问题

热门标签

最新问答