| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
155 Posts |
 Posted - 10/08/2012 : 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
India
47099 Posts |
Posted - 10/08/2012 : 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
155 Posts |
Posted - 10/09/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 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
155 Posts |
Posted - 10/09/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 12:41:17
|
Sorry about that. Declaring and assigning a value to a local variable in one statement works only in SQL 2008 and laterDECLARE @x VARCHAR(32);
SET @x = '20120801';
SELECT
MONTH(CONVERT(DATETIME,@x,112)),
MONTH(CAST(@x AS DATETIME))
|
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 10/09/2012 : 13:36:38
|
| yes It works now.....Thanks a lot Sunitabeck |
Edited by - learning_grsql on 10/09/2012 13:37:15 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 10/09/2012 : 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
155 Posts |
Posted - 10/10/2012 : 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
|
Edited by - learning_grsql on 10/10/2012 05:11:06 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/10/2012 : 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
155 Posts |
Posted - 10/10/2012 : 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 |
Edited by - learning_grsql on 10/10/2012 12:35:11 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/10/2012 : 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
India
47099 Posts |
Posted - 10/10/2012 : 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/
|
|
|
| |
Topic |
|
arithmetic overflow error while converting date
