error - conversion of nvarchar to datetime data type 1

[PeteBull]

I am getting the following error message when attempting to sort results by a datetime column:
"Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value"

here is the query:
select top 100 *
FROM

order by[Event_End]

I'm even getting this error while trying to track down this error...
select *
FROM

where ISDATE([event_end])=0

"Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

This is on SQL Server 2008.

can anyone try to help me track this down?

 

[gmmastros]

Try running this and posting the results here:

Select system_type_id, is_computed
From   sys.columns
Where  object_id = object_id('YourTableName')
		and name = 'YourColumnName'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Can you script DDL of the table? Looks like you may have a computed column in the table.

PluralSight Learning Library

 

[PeteBull]

Select system_type_id, is_computed
From sys.columns
Where object_id = object_id('table')
and name = 'event_end'

system_type_id = 61
is_computed = 0

 

[gmmastros]

system type id 61 is DateTime. You can tell by running this: Select * From sys.types

is_computed = 0 means it is not a computed column.

My next guess is that you are using a view instead of a table. Can you run this?

Select TABLE_TYPE From information_schema.tables Where Table_Name = 'YourTableNameHere'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PeteBull]

yes it is a view

 

[gmmastros]

Can you paste the code for the view? My guess is that the view is converting an nvarchar column to a datetime column, but the data in the nvarchar column does not represent a valid date.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PeteBull]

Here is the code for the view:

SELECT
cond_code,
CONVERT(DATETIME, SUBSTRING(date, 1, 2) + '/' + SUBSTRING(date, 3, 2) + '/20' + SUBSTRING(date, 5, 2) + ' ' + SUBSTRING(time, 1, 2) + ':' + SUBSTRING(time, 3, 2) + ':00') AS Event_End,
CONVERT(INT, LEFT(RIGHT('000000' + CONVERT(NVARCHAR(6), sec_dur), 6), 2)) * 60 * 24 + CONVERT(INT,SUBSTRING(RIGHT('000000' + CONVERT(NVARCHAR(6), sec_dur), 6), 3, 2)) * 60 + RIGHT(sec_dur, 2) AS sec_dur,
clg_num,
in_trk_code,
dialed_num,
code_used,
vdn,
Auth_Code,
Time_Stamp
FROM dbo.raw_pbx_events

 

[markros]

In your original table what types are for date and time columns?

Also, it's better to construct ISO format for dates, e.g.

yyyy-mm-ddThh:mm:ss

See

http://msdn.microsoft.com/en-us/library/ms187928.aspx?wa=wsignin1.0

PluralSight Learning Library

 

[PeteBull]

date = nchar(6)
time = nchar(4)

 

[gmmastros]

Unless I am mistaken, you should be able to find your bad data with this query:

SELECT *
FROM   dbo.raw_pbx_events 
Where  IsDate(SUBSTRING(date, 1, 2) + '/' + SUBSTRING(date, 3, 2) + '/20' + SUBSTRING(date, 5, 2) + ' ' + SUBSTRING(time, 1, 2) + ':' + SUBSTRING(time, 3, 2) + ':00') = 0

If you post some sample data from the date column, I can probably help you modify the view so that it does not return bad dates. I only need a couple values from the date column.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PeteBull]

George, that worked like a charm! Thanks for all of your help in tracking this issue down.

The offending row had a 00 in the month position. That row is gone now (no loss as the entire row was corrupt).

I appreciate your offer to help with the view. Here are the date and time column for the top 10 rows:
date time
022610 1556
022610 1602
022610 1607
022610 1611
022610 1617
022610 1621
022610 1626
022610 1633
022610 1637
022610 1645

 

[gmmastros]

Personally, I would change the view in such a way that NULL is returned if the date or time column represent invalid dates or times. Like this:

SELECT cond_code,
       Case When IsDate('20' + Right(Date, 2) + left(Date, 4)) = 1 
            Then Convert(DateTime, '20' + Right(Date, 2) + left(Date, 4))
            End
       + Case When IsDate(Stuff(Time, 3, 0, ':')) = 1 
            Then Convert(DateTime, Stuff(Time, 3, 0, ':'))
            End As Event_End, 
       CONVERT(INT, LEFT(RIGHT('000000' + CONVERT(NVARCHAR(6), sec_dur), 6), 2)) * 60 * 24 +         CONVERT(INT,SUBSTRING(RIGHT('000000' + CONVERT(NVARCHAR(6), sec_dur), 6), 3, 2)) * 60 + RIGHT(sec_dur, 2) AS sec_dur, 
       clg_num, 
       in_trk_code, 
       dialed_num, 
       code_used, 
       vdn, 
       Auth_Code, 
       Time_Stamp
FROM   dbo.raw_pbx_events

I was testing the code for the date/time conversion with this:

Declare @Date nchar(6)
Declare @Time nchar(4)

Select @Date = '022610', @Time = '1556'

Select Case When IsDate('20' + Right(@Date, 2) + left(@Date, 4)) = 1 
            Then Convert(DateTime, '20' + Right(@Date, 2) + left(@Date, 4))
            End
       + Case When IsDate(Stuff(@Time, 3, 0, ':')) = 1 
            Then Convert(DateTime, Stuff(@Time, 3, 0, ':'))
            End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PeteBull]

Thanks for your help George. I have implemented your changes to the view and tested successfully.

Best of all, I learned something new today.