SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 arithmetic overflow error while converting date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/08/2012 :  16:49:52  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/08/2012 :  19:42:39  Show Profile  Reply with Quote
try
month(convert(varchar(8),convert(datetime,datecolumn,112),112))


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

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/09/2012 :  01:48:44  Show Profile  Reply with Quote
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)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  07:19:32  Show Profile  Reply with Quote
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))
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/09/2012 :  11:14:40  Show Profile  Reply with Quote
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?



Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  12:41:17  Show Profile  Reply with Quote
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))
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/09/2012 :  13:36:38  Show Profile  Reply with Quote
yes It works now.....Thanks a lot Sunitabeck

Edited by - learning_grsql on 10/09/2012 13:37:15
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2012 :  23:34:00  Show Profile  Reply with Quote
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/

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/10/2012 :  05:10:25  Show Profile  Reply with Quote
@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


Edited by - learning_grsql on 10/10/2012 05:11:06
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/10/2012 :  07:00:34  Show Profile  Reply with Quote
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.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/10/2012 :  12:34:46  Show Profile  Reply with Quote
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

Edited by - learning_grsql on 10/10/2012 12:35:11
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/10/2012 :  13:57:02  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2012 :  23:52:36  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000