Solved

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

Posted on 2010-11-28
19
267 Views
Last Modified: 2012-05-10
Please see attached.
ee-query.doc
0
Comment
Question by:ejr19592004
  • 9
  • 4
  • 4
  • +2
19 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34227759
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
 

Author Comment

by:ejr19592004
ID: 34227776
this be in the form of a store proc? can you embellish?
0
 
LVL 4

Expert Comment

by:parthmalhan
ID: 34227817
Provide more data to figure out error.
You can provide data in Excel sheet.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ejr19592004
ID: 34227846
Herewith the source field and target (in red)
Sample.xls
0
 
LVL 2

Expert Comment

by:svalekar
ID: 34228399
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
 
LVL 2

Expert Comment

by:svalekar
ID: 34228401
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
 
LVL 2

Expert Comment

by:svalekar
ID: 34228407
wait little change Replace EventDateTime WIth CONVERT(CHAR(8),eventdatetime,12)
0
 

Author Comment

by:ejr19592004
ID: 34228509
Is there anything less messy than the above solution?
0
 
LVL 2

Expert Comment

by:svalekar
ID: 34228776
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
 

Author Comment

by:ejr19592004
ID: 34228994
I will give it a spin in the morning. ta
0
 

Author Comment

by:ejr19592004
ID: 34234118
@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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34234447
Perhaps something like this:
CONVERT(datetime, LEFT(EventDateTime, 8), 1)

Or if you want to time as well:
CONVERT(datetime, REPLACE(EventDateTime, '-', ''), 1)
0
 

Author Comment

by:ejr19592004
ID: 34234660
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34235621
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34235625
And that should have read:
It looks like you are not using MDY but rather DMY,
0
 

Author Comment

by:ejr19592004
ID: 34235777
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
 

Author Comment

by:ejr19592004
ID: 34235791
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 75 total points
ID: 34236039
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
 

Author Closing Comment

by:ejr19592004
ID: 34236053
spot on. thanks for the tip. 5 lines of rubbish crept into the file.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question