Sql-server – How to find out which records are causing an exception in a select statement

error handlingexceptionselectsql serversql-server-2016

I have a select query that I use as part of an insert statement,
but it raises the following exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Here is the definition of the table I want to insert to:

IF OBJECT_ID('[dbo].[tbl_crbHistory]') IS NOT NULL 
DROP TABLE [dbo].[tbl_crbHistory] 
GO
CREATE TABLE [dbo].[tbl_crbHistory] ( 
[Id]               INT              IDENTITY(1,1)   NOT NULL,
[ClientReference]  VARCHAR(50)                      NOT NULL,
[Status]           INT                              NOT NULL,
[DateAdded]        DATETIME                         NOT NULL  
            CONSTRAINT [DF__tbl_crbHi__DateA__4F8DAFFA] DEFAULT (getdate()),
CONSTRAINT   [PK_tbl_crbHistory]  PRIMARY KEY CLUSTERED    ([Id] asc),
CONSTRAINT   [FK_tbl_crbHistory_ref_crbStatus]   
            FOREIGN KEY ([Status]) REFERENCES [ref_crbStatus]([statusId]))

GO

CREATE NONCLUSTERED INDEX [idx_crbDateAdded] 
   ON [dbo].[tbl_crbHistory] ([DateAdded] desc)
   INCLUDE ([Status], [ClientReference])
CREATE NONCLUSTERED INDEX [idx_clienteReferenceStatus] 
   ON [dbo].[tbl_crbHistory] ([ClientReference] asc)
   INCLUDE ([Status], [DateAdded])
GO

EXEC sys.sp_addextendedproperty
          @name = N'MS_Description', @value = N'Stores history of CRB status change',
          @level0type = N'SCHEMA', @level0name = [dbo],
          @level1type = N'TABLE', @level1name = [tbl_crbHistory];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'Primary Key',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [Id];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', 
         @value = N'unique id for each applicat per organisationId',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [ClientReference];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'CRB Status',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [Status];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'Time stamp',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [DateAdded];

the complete statement, including the select and the insert:

INSERT INTO tbl_crbHistory (clientReference, status, dateAdded)
SELECT tc.pushedClientReference as ClientReference, 
       tc.pushedStatusId as [Status], 
       tc.createdOn as DateAdded 
FROM tablebackups.dbo.tmp_colagbgdata tc
LEFT JOIN tbl_application_crb_initialData ci
ON tc.PushedClientReference = ci.ClientReference
WHERE ci.clientReference <> ''
AND tc.pushedStatusId  IN ( SELECT STATUSID FROM dbo.ref_crbStatus)

The column tc.createdOn is a datetime and it is breaking my code due to the error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Is there any way(s) I can select the record(s) that are causing this?

If I could find these records in this or similar situations, I could deal with them and re-run my query at a later time.

Best Answer

Try this query:

SELECT tc.pushedClientReference as ClientReference, tc.pushedStatusId as [Status], 
tc.createdOn 
FROM tablebackups.dbo.tmp_colagbgdata tc
where TRY_CAST(tc.createdOn as datetime) is null
and tc.createdOn is not null

The TRY_CAST function returns null when a CAST operation is impossible, so the above query will return the rows where converting the createdOn column to datetime is causing an error.

If you have a particular preference there's also TRY_CONVERT.

Now that we know the SELECT query is feeding an INSERT, I'd suggest that tc.createdon in the SELECT list be explicitly converted using TRY_CAST or TRY_CONVERT.

Related Solutions

Sql-server – Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*) 
  from dbo.profile 
  where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Best Answer

Related Solutions

Sql-server – Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Sql-server – deteriorating stored procedure running times

Related Question