arithmetic overflow error converting to datetime

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 converting to datetime

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-18 : 11:13:49

Hi,

I have a date column with varchar(50) as datatype with values like
01/08/2012
02/08/2012
03/08/2012

When I try to convert this in '20120801' format using the code below, i get the error arithmetic overflow error converting expression to data type datetime

select convert(varchar(8),cast(date as datetime),112) as date, field1 from mytable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 11:19:17

no need of inner cast


select convert(varchar(8),[date],112) as date, field1 from mytable

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 11:22:06

b/w one question is the original values in dd/mm/yyyy or mm/dd/yyyy format?

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-18 : 11:22:55

[code]select convert(varchar(8),[date],112) as date, field1 from mytable[/code]
The above code displays date as
'01/08/20'
'02/08/20'
'03/08/20'

But I want output as
'20120801'
'20120802'
'20120803'

@visakh
I just read your second post..
original values are 'dd/mm/yyyy'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 11:28:07

[code]
select convert(varchar(8),convert(datetime,[date],103),112) as [date], field1 from mytable
[/code]

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-18 : 13:21:11

As usual, many thanks visakh16. It works now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 15:16:47

welcome

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-03 : 10:09:46

1 Always use proper DATETIME datatype to store dates
2 Formation matters only when you want to show them somewhere

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 15:12:31

i suggested this already but this was the reason OP told for that

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=178038

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources