How to find out which records are causing an exception in a select statement?

Question

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.

Answer 1

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.