Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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