arithmetic overflow error while converting date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

arithmetic overflow error while converting date

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-08 : 16:49:52

I have a date column with datatype nchar as below

20120801
20120802
20120803
etc.

When I try the code below to extract only month from the date, I get error "
arithmetic overflow error while converting....."


month(convert(varchar(6),convert(datetime,datecolumn,112),112))

I know one of the solution is to convert the date column to datatype "date". But that is something I cannot do it immediately for some reason but of course I will change it soon.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-08 : 19:42:39

try
month(convert(varchar(8),convert(datetime,datecolumn,112),112))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-09 : 01:48:44

Im still getting the same error - Arithmetic overflow error converting expression to data type datetime.

I just noticed, in the first place, the code below itself not working


convert(varchar(8),convert(datetime,datecolumn,112),112)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-09 : 07:19:32

MONTH function requires a date/datetime data type as the argument. So use one of these:

DECLARE @x VARCHAR(32) = '20120801';

SELECT 
	MONTH(CONVERT(DATETIME,@x,112)),
	MONTH(CAST(@x AS DATETIME))

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-09 : 11:14:40

thanks @sunitabeck
But I get error "can't assign a default value to a local variable" for your code.

In my case, even without using month function, the code doesn't work for my code


convert(varchar(8),convert(datetime,datecolumn,112),112)

Is it because simply sql server cannot understand the format or my code is incorrect?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-09 : 12:41:17

Sorry about that. Declaring and assigning a value to a local variable in one statement works only in SQL 2008 and later

DECLARE @x VARCHAR(32);
SET @x = '20120801';

SELECT 
	MONTH(CONVERT(DATETIME,@x,112)),
	MONTH(CAST(@x AS DATETIME))

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-09 : 13:36:38

yes It works now.....Thanks a lot Sunitabeck

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 23:34:00

quote:
Originally posted by sunitabeck

MONTH function requires a date/datetime data type as the argument. So use one of these:
DECLARE @x VARCHAR(32) = '20120801';

SELECT
MONTH(CONVERT(DATETIME,@x,112)),
MONTH(CAST(@x AS DATETIME))

Not exactly. so far as format is proper and unambiguos it works
this works for me anyways!


declare @date varchar(50)='20120501' 
select month(convert(varchar(8),convert(datetime,@date,112),112))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-10 : 05:10:25

@visakh
Your code works for me also but when I replace @date with my actual date column in table1, i get the same arithmetic flow error. So I think the Sql Server 2005 cannot understand my date column properly.


select month(convert(varchar(8),convert(datetime,t1.date,112),112)) from table1 t1

My dates are stored as varchar(50) datatype in the following format :
20120801
20120802
20120803

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 07:00:34

quote:
Originally posted by learning_grsql

@visakh
Your code works for me also but when I replace @date with my actual date column in table1, i get the same arithmetic flow error. So I think the Sql Server 2005 cannot understand my date column properly.

select month(convert(varchar(8),convert(datetime,t1.date,112),112)) from table1 t1

My dates are stored as varchar(50) datatype in the following format :
20120801
20120802
20120803

If all your dates are in the YYYYMMDD format, that should not happen. The three examples you posted works correctly for the code you posted. Can you post an example of the data that causes it to fail?

Regardless, I would still use the code I posted. MONTH function requires a date/time argument or something that can be resolved to it. If you give it a string argument as you are doing in this case, it has to resolve it to a valid date/time. So in effect you are doing conversions twice, once in the outer convert that is in your code to convert datetime to varchar(8) and a second time implicitly to convert it back to datetime.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-10 : 12:34:46

quote:
Originally posted by sunitabeck

quote:
Originally posted by learning_grsql

@visakh
Your code works for me also but when I replace @date with my actual date column in table1, i get the same arithmetic flow error. So I think the Sql Server 2005 cannot understand my date column properly.

select month(convert(varchar(8),convert(datetime,t1.date,112),112)) from table1 t1

My dates are stored as varchar(50) datatype in the following format :
20120801
20120802
20120803

If all your dates are in the YYYYMMDD format, that should not happen. The three examples you posted works correctly for the code you posted. Can you post an example of the data that causes it to fail?

As usual, again you helped me. This is a very valid point. I have also values "date" in the column date. Every time we upload data we upload with header also. It works fine as soon as I delete them.

Thank you very much Sunitabeck and Visakh16

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 13:57:02

Glad you got to the bottom of it :)

If you are importing data using SSIS, bulk insert etc., you can specify the first row to be imported, which would allow you to skip the header row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-10 : 23:52:36

quote:
Originally posted by learning_grsql

quote:
Originally posted by sunitabeck

quote:
Originally posted by learning_grsql

@visakh
Your code works for me also but when I replace @date with my actual date column in table1, i get the same arithmetic flow error. So I think the Sql Server 2005 cannot understand my date column properly.

select month(convert(varchar(8),convert(datetime,t1.date,112),112)) from table1 t1

My dates are stored as varchar(50) datatype in the following format :
20120801
20120802
20120803

If all your dates are in the YYYYMMDD format, that should not happen. The three examples you posted works correctly for the code you posted. Can you post an example of the data that causes it to fail?

As usual, again you helped me. This is a very valid point. I have also values "date" in the column date. Every time we upload data we upload with header also. It works fine as soon as I delete them.

Thank you very much Sunitabeck and Visakh16

that will be regarded as spurious value and hence causes it to break.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources