ejr19592004
asked on
Data type resulted in an out-of-range datetime value.
Please see attached.
ee-query.doc
ee-query.doc
ASKER
this be in the form of a store proc? can you embellish?
Provide more data to figure out error.
You can provide data in Excel sheet.
You can provide data in Excel sheet.
ASKER
Herewith the source field and target (in red)
Sample.xls
Sample.xls
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
Convert(DateTime, Left(EventDateTime,2) + '-' + Left(DateName(mm, '01' + '-' + Substring(EventDateTime, CHARINDEX('/', EventDateTime) + 1, 2) + '-' + Right(EventDateTime,2)),3)
See above querty is working or not
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
Convert(DateTime, Left(EventDateTime,2) + '-' + Left(DateName(mm, '01' + '-' + Substring(EventDateTime, CHARINDEX('/', EventDateTime) + 1, 2) + '-' + Right(EventDateTime,2)),3)
See above query is working or not
wait little change Replace EventDateTime WIth CONVERT(CHAR(8),eventdatet ime,12)
ASKER
Is there anything less messy than the above solution?
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), eventdatet ime,12), CHARINDEX('/', CONVERT(CHAR(8),eventdatet ime,12)) + 1, 2) + '-' + Right(CONVERT(CHAR(8),even tdatetime, 12),2)),3) + '-' + Right(CONVERT(CHAR(8),even tdatetime, 12),2) , 103 )
Update dbo.dataset set eventdate = Convert(DateTime, Left(EventDateTime,2) + '-' + Left(DateName(mm, '01' + '-' + Substring(CONVERT(CHAR(8),
ASKER
I will give it a spin in the morning. ta
ASKER
@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.
i am in sydney, the time right now is 8:26 am.
Perhaps something like this:
CONVERT(datetime, LEFT(EventDateTime, 8), 1)
Or if you want to time as well:
CONVERT(datetime, REPLACE(EventDateTime, '-', ''), 1)
CONVERT(datetime, LEFT(EventDateTime, 8), 1)
Or if you want to time as well:
CONVERT(datetime, REPLACE(EventDateTime, '-', ''), 1)
ASKER
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.
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.
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)
UPDATE dbo.dataset
SET eventdate = CONVERT(datetime, LEFT(EventDateTime, 8), 3)
And that should have read:
It looks like you are not using MDY but rather DMY,
It looks like you are not using MDY but rather DMY,
ASKER
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.
ASKER
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.
= 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
spot on. thanks for the tip. 5 lines of rubbish crept into the file.
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.