• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Data type resulted in an out-of-range datetime value.

Please see attached.
ee-query.doc
0
ejr19592004
Asked:
ejr19592004
  • 9
  • 4
  • 4
  • +2
1 Solution
 
cyberkiwiCommented:
declare @evdatetime varchar(100)
set @evdatetime = '01/10/10 - 08:57'
select convert(char(8), @evdatetime, 12)


This works for the value in your sample.
I suspect there are other rows in the data that cannot be converted, but it won't be easy to find them.
0
 
ejr19592004Author Commented:
this be in the form of a store proc? can you embellish?
0
 
parthmalhanCommented:
Provide more data to figure out error.
You can provide data in Excel sheet.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
ejr19592004Author Commented:
Herewith the source field and target (in red)
Sample.xls
0
 
svalekarCommented:
update dbo.dataset set eventdate =
Convert(DateTime, Left(EventDateTime,2) + '-' + Left(DateName(mm,  '01' +  '-' + Substring(EventDateTime, CHARINDEX('/', EventDateTime) +  1, 2) + '-' + Right(EventDateTime,2)),3) + '-' + Right(EventDateTime,2) , 103 )

See above querty is working or not
0
 
svalekarCommented:
update dbo.dataset set eventdate =
Convert(DateTime, Left(EventDateTime,2) + '-' + Left(DateName(mm,  '01' +  '-' + Substring(EventDateTime, CHARINDEX('/', EventDateTime) +  1, 2) + '-' + Right(EventDateTime,2)),3) + '-' + Right(EventDateTime,2) , 103 )

See above query is working or not
0
 
svalekarCommented:
wait little change Replace EventDateTime WIth CONVERT(CHAR(8),eventdatetime,12)
0
 
ejr19592004Author Commented:
Is there anything less messy than the above solution?
0
 
svalekarCommented:
Ya above one is little messy but u just try it on your sample table. U have to execute the below query and let me know it is working or not

Update dbo.dataset set eventdate = Convert(DateTime, Left(EventDateTime,2) + '-' + Left(DateName(mm,  '01' +  '-' + Substring(CONVERT(CHAR(8),eventdatetime,12), CHARINDEX('/', CONVERT(CHAR(8),eventdatetime,12)) +  1, 2) + '-' + Right(CONVERT(CHAR(8),eventdatetime,12),2)),3) + '-' + Right(CONVERT(CHAR(8),eventdatetime,12),2) , 103 )
0
 
ejr19592004Author Commented:
I will give it a spin in the morning. ta
0
 
ejr19592004Author Commented:
@svalekar works. but i was hoping for something more elegant. if i don't get one by the cob today i will award the points to svalekar.

i am in sydney, the time right now is 8:26 am.
0
 
Anthony PerkinsCommented:
Perhaps something like this:
CONVERT(datetime, LEFT(EventDateTime, 8), 1)

Or if you want to time as well:
CONVERT(datetime, REPLACE(EventDateTime, '-', ''), 1)
0
 
ejr19592004Author Commented:
The full statement goes like this for the date portion:

Update dbo.dataset set eventdate = CONVERT(datetime, LEFT(EventDateTime, 8), 1)

Mr. SQL Server said this:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Nice try. Sorry I would have liked it to have worked.
0
 
Anthony PerkinsCommented:
It looks like you are now using MDY but rather DMY, therefore you should use:
UPDATE  dbo.dataset
SET     eventdate = CONVERT(datetime, LEFT(EventDateTime, 8), 3)
0
 
Anthony PerkinsCommented:
And that should have read:
It looks like you are not using MDY but rather DMY,
0
 
ejr19592004Author Commented:
That's correct. And this works as a read, SELECT CONVERT(CHAR(08), eventdatetime, 101) FROM dbo.dataset, but when I incorporate into an update I get an out of range.

0
 
ejr19592004Author Commented:
I tried this: update dbo.datasset set eventdate
 = CONVERT(datetime, LEFT(EventDateTime, 8), 3)

I got this:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
0
 
Anthony PerkinsCommented:
I tested it with your sample data and I did not have any problems, so it obviously means you have invalid data or dates that are out of range.  Since we do not have your real data it is up to you to find it.
0
 
ejr19592004Author Commented:
spot on. thanks for the tip. 5 lines of rubbish crept into the file.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 9
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now