November 28, 2007 at 10:21 am
Hello everyone! I have a table that have a field type nvarchar(6) and it suppost to be dates, the way that the data is display is like this 112807 and not all populated it contains blanks - how do I canvert it into a redable 11/27/2007. I tried this CONVERT(datetime, ODCDAT,102) and CAST(ODCDAT as datetime), and I get this error "Arithmetic overflow error converting expression to data type datetime."
Any suggestions..
Thanks
November 28, 2007 at 10:28 am
Look at the function stuff and to put in the / in the correct places then try convert/cast.
November 28, 2007 at 10:39 am
the error means there is dat in your column that it cannot convert to a valid date.
select case when yourcol = '' or yourcol is null then null
else convert(datetime, yourcol, format ) -- (see BOL))
end
from ...
or
select substring (yourcol, 1,2) + '/' + substring (yourcol, 3,2) + '/' + substring (yourcol, 5,2)
from ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 28, 2007 at 10:41 am
Try something like this
DECLARE @MyDate NVARCHAR(6)
SET @MyDate = '112807'
SELECT CONVERT(DATETIME, SUBSTRING(@MyDate,5,2) + SUBSTRING(@MyDate,1,2) + SUBSTRING(@MyDate,3,2),101)
November 28, 2007 at 10:50 am
Loads of ways to do, here is what I was talking about.
DECLARE @MyDate NVARCHAR(6)
SET @MyDate = '112807'
SELECT CAST(STUFF(STUFF(@MyDate,5,0,'/'),3,0,'/') as DATETIME)
November 28, 2007 at 11:09 am
thanks, here is the line I entered and gave me the same error!
CASE when ODCDAT = ' ' or ODCDAT is null then null
else convert(datetime, ODCDAT,102)END AS LastDateUsed,
November 28, 2007 at 11:25 am
102 in a convert statement expects the date to be like this yy.mm.dd, you need to change the order of the dd mm yy parts of the date before passing it into convert.
Allen
December 28, 2007 at 4:00 am
maynor_ramirez (11/28/2007)
Hello everyone! I have a table that have a field type nvarchar(6) and it suppost to be dates, the way that the data is display is like this 112807 and not all populated it contains blanks - how do I canvert it into a redable 11/27/2007. I tried this CONVERT(datetime, ODCDAT,102) and CAST(ODCDAT as datetime), and I get this error "Arithmetic overflow error converting expression to data type datetime."Any suggestions..
Thanks
1 Always use proper DAETTIME datatype to store dates
2 Always leave formation work at the front end application(If used)
3 Use universal date format YYYYMMDD to avoid conflict with local settings
4 If you cant change the datatype, create new column with DATETIME datatype and update it from nvarcha column and use that DATETIME column for further manipulation
Failing to plan is Planning to fail
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply