| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
155 Posts |
 Posted - 08/18/2012 : 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 |
Edited by - learning_grsql on 08/18/2012 11:14:36
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/18/2012 : 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/
|
Edited by - visakh16 on 08/18/2012 11:19:30 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/18/2012 : 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
155 Posts |
Posted - 08/18/2012 : 11:22:55
|
select convert(varchar(8),[date],112) as date, field1 from mytable
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' |
Edited by - learning_grsql on 08/18/2012 11:26:21 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/18/2012 : 11:28:07
|
select convert(varchar(8),convert(datetime,[date],103),112) as [date], field1 from mytable
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 08/18/2012 11:28:36 |
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/18/2012 : 13:21:11
|
| As usual, many thanks visakh16. It works now. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/18/2012 : 15:16:47
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/03/2012 : 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
India
47157 Posts |
|
| |
Topic |
|
arithmetic overflow error converting to datetime
