Ошибка arithmetic overflow error converting expression to data type datetime - Ремонт и установка крупной бытовой техники

While executing following error is showing

declare @yr_mnth_dt as numeric;
set @yr_mnth_dt = 20130822;
select convert(datetime,@yr_mnth_dt,112) as YR_MNTH_DT

error shows

Arithmetic overflow error converting expression to data type datetime.

asked Aug 24, 2013 at 8:40

You issue is that you’re trying to convert the numeric to a datetime, and this just isn’t working.

You need to turn your numeric into a string first:

declare @yr_mnth_dt as numeric;
set @yr_mnth_dt = 20130822;

select yr_mnth_dt = cast(cast(@yr_mnth_dt as char(8)) as datetime);

SQL Fiddle with demo.

When you try and convert a numeric type to a datetime, SQL Server tries to add the numeric value as the number of days to the date 01-Jan-1900. In your case this is trying to add millions of days, and hence the overflow error.

CONVERT works fine, too, if you prefer:

select yr_mnth_dt = convert(datetime, convert(char(8), @yr_mnth_dt));

SQL Fiddle with demo.

answered Aug 24, 2013 at 11:07

Ian PrestonIan Preston

38.6k8 gold badges95 silver badges91 bronze badges

I’ve only seen the conversion used for strings. I can’t easily tell whether it’s even designed to work with numbers. You could convert the number to a string, then the string to a date. However, I would personally just use DATEFROMPARTS:

SELECT DATEFROMPARTS(@yr_mnth_dt / 10000, 
                     (@yr_mnth_dt / 100) % 100,
                     @yr_mnth_dt % 100) AS YR_MNTH_DT

answered Aug 24, 2013 at 10:38

Jon SkeetJon Skeet

1.4m862 gold badges9104 silver badges9178 bronze badges

Why numeric?
Try this

declare @yr_mnth_dt as varchar(10);
set @yr_mnth_dt = '20130822';
select convert(datetime,@yr_mnth_dt,112) as YR_MNTH_DT

answered Aug 24, 2013 at 15:29

msi77msi77

1,5821 gold badge10 silver badges10 bronze badges

Now we can do simply:

declare @yr_mnth_dt as numeric;
set @yr_mnth_dt = 20130822;
select cast(str(@yr_mnth_dt) as datetime); // output: 2013-08-22 00:00:00.000

answered Apr 27 at 12:18

mohammadAlimohammadAli

3652 silver badges13 bronze badges

Источник

Remove From My Forums

Question
Unable to conver it…

create procedure temp(empid int, todaydate datetime)

as

begin

select * from table where todaydate = lastdate;

end

here lastdate
is INT TYPE

getting Arithmetic overflow error converting expression to data type datetime.

pls help it.

Answers

```
CREATE PROCEDURE temp (
	empid INT
	,todaydate DATETIME
	)
AS
BEGIN
	SELECT *
	FROM mytable
	WHERE todaydate = cast(lastdate AS DATETIME)
END
```
-Vaibhav Chaudhari
- Marked as answer by
  
  Thursday, November 27, 2014 2:56 PM

Источник

I have such an issue. I want to select a column PDWEDT (it is numeric) and I need to select previous Saturday. The way how I usually do is with the help of declare statement

Text

DECLARE @CurrentWeekday INT = DATEPART(WEEKDAY, GETDATE())

DECLARE @LastSunday DATETIME = DATEADD(day, -1 * (( @CurrentWeekday % 7) - 1), GETDATE());

Afterwards, I have some code and later I just try to select the dates

Text

 AND  p.[PDWEDT] = @LastSunday

Not sure why but I am getting an error: «Arithmetic overflow error converting expression to data type datetime.»

So I see 2 potential ways to solve this problem.

1. Either to find another way to select previous Saturday instead of DECLARE statements (though as I understand it is the most common way). or 2. To cast or convert the field, however I am not sure how it can be done since it is numeric at the very moment. I will appreciate any ideas. Thank you. p/s I use SQL Server Management Studio.

check
Best Answer

Author jrp 78

jrp78

This person is a Verified Professional

This person is a verified professional.

Verify your account
to enable IT peers to see that you are a professional.

ghost chili

Microsoft SQL Server Expert

check
37
Best Answers
thumb_up
89
Helpful Votes

Ahh, the code was getting sunday not saturday, try this.

EDIT: one correction

SQL

CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT ))  = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)

1 found this helpful
thumb_up
thumb_down

View Best Answer in replies below

Read these next…

Windows 10 Enterprise not allowing downloads or search

Windows

Hello All,We just got a new machine that came with its own computer. The computer has Windows 10 Enterprise on it. All of our PCs are running Windows 10 Pro. I joined the new PC to the domain and logged in as the domain admin. Everything worked great. The…
Bring your dog to work day

Holidays

Do you work from home or office? Doesn’t matter. Let’s see those pets that work right alongside you. Well, mine mostly sleeps but they are there! This is Peanut. Her bed is next to my desk. I have to walk her up and down the stairs due to her rickety bon…
Snap! — Space Immunities, Stretching Metal, Defining Sandwich, 3D Printed Steel

Spiceworks Originals

Your daily dose of tech news, in brief.

Welcome to the Snap!

Flashback: june 23, 1912: Computer Pioneer Alan Turing Born (Read more HERE.)

Bonus Flashback: June 22, 2000: Possible evidence of liquid water found on Mars (Read more HERE.)
…
Has anyone else heard anything about RHEL becoming closed source?

Linux

Would anyone care to share their thoughts about what this might indicate for things to come?

A few weeks ago, Jay Lecroix Opens a new window made some passing references to the changes IBM has made to CentOS/CentOS Strea…
IT Documentation

Software

Let me begin by stating I am aware that there are tonnes of posts for this question. But, I was denied financial leeway for unnecessary expenses; and this in their opinion is one. I, recently, acquired the role of and IT do-it-all. for more than a decade,…

Источник

I’m currently importing a table from MYSQL to MSSQL. There’s a column there for storing date, only it’s stored as an number. When i import it to MSSQL i get it in as an int data type.

When i try to convert that to datetime I get an :

Arithmetic overflow error converting
expression to data type datetime.

Since i’m using MSSQL 2005 I can’t use datetime2 and fit it so it does not overflow.

Is there a way to trim that date stored as an int so it does not overflow the datetime format ?

asked Jan 14, 2010 at 18:17

The MSSQL datetime data type can take values from January 1, 1753 to December 31, 9999. The integer equivalents for those values are -53690 and 2958463 respectively. Trying to convert integer values outside that range would cause the arithmetic overflow.

answered Jan 14, 2010 at 19:27

Ryan BolgerRyan Bolger

16.7k3 gold badges42 silver badges63 bronze badges

Источник

I don’t know if anyone has given you the Stern Lecture for using a non-datetime datatype to contain datetime data, but if not, consider it done.

That being said, and with the realization that we can’t always control the form of the data sent to us from outside our domain, here is one solution. I’ve even fixed the IsDate problem. By using date arithmetic rather than conversion (limiting CONVERT to change the numeric to integer so we can use modulo), all values between the two extremes will generate a valid date — although, if someone entered the date 20080231 which is not a valid date, the result will be 2008-02-29, which is a valid date but not necessarily the correct date (the date the user meant to enter). Since this will not result in an error, it may not be the result you want.

declare @test-2 table(

FirstDispursementDate numeric( 8, 0 )

);

Insert @test-2( FirstDispursementDate )

select 20020405 union all

select 20041222 union all

select 20001111 union all

select 20060223 union all

select 19991110 union all

select 19980425 union all

select 19991118 union all

select 20080723 union all

select 20020813 union all

select 17500723 union all -- Bogus date

select 20070231 union all -- Looks bad but converts to 2007-02-28

select 20029999 union all -- Looks bad but converts to 2010-06-09

select 20029901 union all -- Looks bad but converts to 2010-03-01

select 99991231 union all

select 0; -- Bogus date

-- All calculations in one statement

select FirstDispursementDate as AsNumeric,

DateAdd( mm, ((Convert( int, FirstDispursementDate / 10000 ) - 1900) * 12 )

+ ((Convert( int, FirstDispursementDate ) % 10000) / 100) - 1,

DateAdd( dd, (Convert( int, FirstDispursementDate ) % 100) - 1, 0 ))

as AsDatetimeValue

from @test-2

where FirstDispursementDate between 17530101 and 99991231; -- Fullproof "IsDate" function

-- The same calculations but separated into nested derived tables for purposes

-- of illustration only.

-- First convert numeric to int (x), then split int value into three values for

-- year, month and day (y) and finally manipulate to make a datetime value.

select DateAdd( mm, ((FDDYear - 1900) * 12) + FDDMonth - 1,

DateAdd( dd, FDDDay - 1, 0 )) as AsDatetimeValue

from(

select FirstDispursementDate / 10000 as FDDYear,

FirstDispursementDate % 10000 / 100 as FDDMonth,

FirstDispursementDate % 100 as FDDDay

from(

select Convert( int, FirstDispursementDate ) as FirstDispursementDate

from @test-2

where FirstDispursementDate between 17530101 and 99991231

) x

) y;

Tomm Carr
—
Version Normal Form — http://groups.google.com/group/vrdbms

Источник

Question

Answers

Read these next…

Windows 10 Enterprise not allowing downloads or search

Bring your dog to work day

Snap! — Space Immunities, Stretching Metal, Defining Sandwich, 3D Printed Steel

Has anyone else heard anything about RHEL becoming closed source?

IT Documentation